#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13

    Insert and split currency in sub select


    I wish to insert into a table using select, but I need to split a currency amount between the number of inserts for the select and where there is a balance leftover this needs to be justified to one of the inserts.
    i.e divide 100 between 3 inserts

    tenant_id t_amount
    123 33.33
    456 33.33
    789 33.34

    I currently do the following using PHP but it does not take care of the pennies.
    Code:
    INSERT INTO
     tenant_payment_due
     (
      tenant_id,
      t_amount,
      rent_run_id,
      trans_id,
      r_account_id
     )
       SELECT
       tt_id,
       '-33.33',
       '2012-6-01',
       100,
       '5000'
       FROM
       tenants WHERE tenancy_id = 100 AND tt_rent_split > 0 AND   tt_deleted = 0;
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    This is the kind of logic that you need to keep outside MySQL in your procedural programming language (unless you want to write a stored procedure for it).
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo