Matching debit with credit per transaction in mysql or with better algothrim?

Question

First let me explain table structure and my required output.

userid  date           amount 
123    2017-01-01         5 
123    2017-01-03         2 
124    2017-01-04         2 
124    2017-01-04         3 
123    2017-01-05         -2

Debit transactions are with negative, positive transactions are credit transactions of members. Account statement we can easily made through this query

select date as BookingDate,
       if(amount<0,amount,0) as Debit_Amount,
       if(amount>0,amount,0) as Credit_Amount,
       (@runtot :=  amount  + @runtot) AS Balance  
from transactions,
     (SELECT @runtot:=0) c 
where userid=123


BookingDate  Debit_Amount  Credit_Amount  Balance 
2017-01-01    0                 5           5 
2017-01-03    0                 2           7 
2017-01-05   -2                0           5

My requirement is to mark each transaction paid or partial paid base on debit transactions with FIFO method. Like this. Is this possible through mysql query or with better algorithm?

 userid  date           amount   status
    123    2017-01-01         5   partial_paid(-2)
    123    2017-01-03         2 
    124    2017-01-04         2 
    124    2017-01-04         3 
    123    2017-01-05         -2

Thanks

score 1 · Accepted Answer · answered Feb 15 '17 at 12:56

MariaDB [sandbox]> create table t(userid  int,dt date,  amount int);
Query OK, 0 rows affected (0.28 sec)

MariaDB [sandbox]> truncate table t;
Query OK, 0 rows affected (0.20 sec)

MariaDB [sandbox]> insert into t values
    -> (123  ,  '2017-01-01'  ,       5) ,
    -> (123  ,  '2017-01-03'  ,       2 ),
    -> (124  ,  '2017-01-04'  ,       2) ,
    -> (124  ,  '2017-01-04'  ,       3 ),
    -> (123  ,  '2017-01-05'  ,       -2),
    -> (125  ,  '2017-01-01'  ,       5) ,
    -> (125  ,  '2017-01-03'  ,       2 ),
    -> (125  ,  '2017-01-05'  ,       -6),
    -> (126  ,  '2017-01-01'  ,       5) ,
    -> (126  ,  '2017-01-02'  ,       -10),
    -> (126  ,  '2017-01-03'  ,       2 ),
    -> (126  ,  '2017-01-05'  ,       -10),
    -> (126  ,  '2017-01-06'  ,       13);
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select s.userid,s.dt,s.amount,
    ->  case when s.crs is null then 0 else s.crs end crs,
    ->  case when s.exhaust is null then 0 else s.exhaust end exhaust,
    ->  case when s.amount > 0 and s.amount <= s.crs and s.crs > 0 then 'Fully paid'
    ->  when s.amount > 0 and s.amount > s.crs and s.crs > 0 then concat('Part paid -' ,s.crs)
    ->  else ''
    ->  end msg
    -> from
    -> (
    -> select t1.*,
    ->  if(t1.userid <> @p ,
    ->   @crs:=(select sum(t2.amount)  * - 1 from t t2 where t2.userid = t1.userid and t2.amount < 0)
    ->   ,@crs:=@crs)  crs,
    ->   if(t1.amount < 0 ,@crs:=@crs,if (t1.amount > @crs , @crs:=0,@crs:=@crs - t1.amount)) exhaust,
    ->   @p:=t1.userid p
    ->
    -> from  (select @p:=0,@crs:=0) p ,t t1
    -> order by t1.userid, t1.dt
    -> ) s
    -> ;
+--------+------------+--------+------+---------+--------------+
| userid | dt         | amount | crs  | exhaust | msg          |
+--------+------------+--------+------+---------+--------------+
|    123 | 2017-01-01 |      5 | 2    | 0       | Part paid -2 |
|    123 | 2017-01-03 |      2 | 0    | 0       |              |
|    123 | 2017-01-05 |     -2 | 0    | 0       |              |
|    124 | 2017-01-04 |      2 | 0    | 0       |              |
|    124 | 2017-01-04 |      3 | 0    | 0       |              |
|    125 | 2017-01-01 |      5 | 6    | 1       | Fully paid   |
|    125 | 2017-01-03 |      2 | 1    | 0       | Part paid -1 |
|    125 | 2017-01-05 |     -6 | 0    | 0       |              |
|    126 | 2017-01-01 |      5 | 20   | 15      | Fully paid   |
|    126 | 2017-01-02 |    -10 | 15   | 15      |              |
|    126 | 2017-01-03 |      2 | 15   | 13      | Fully paid   |
|    126 | 2017-01-05 |    -10 | 13   | 13      |              |
|    126 | 2017-01-06 |     13 | 13   | 0       | Fully paid   |
+--------+------------+--------+------+---------+--------------+
13 rows in set (0.03 sec)

Beware:- I haven't fully tested this!!

@Salmon thanks for reply but i don't think it will work for millions of records? — Jackson Harry, Feb 16 '17 at 05:11
How would you show a Fully Paid which consisted of n Part Paids? Or a Part Paid which consisted of n Part Paids? — P.Salmon, Feb 16 '17 at 07:44
Date of Last debit transactions(exhaust) which mark status fully paid, make sense? — Jackson Harry, Feb 16 '17 at 07:52

score 1 · Answer 2 · answered Feb 15 '17 at 12:58

You need a junction table to associate the debit transactions and the credit transactions. Something like this:

CREATE TABLE debit_credit_map (
  debit_id INT NOT NULL,
  credit_id INT NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(debit_id, credit_id),
  KEY(credit_id)
)ENGINE=InnoDB;

When you want to apply a credit against a debit you first post the credit, capture its id, then start a loop -- verify how much of the credit is unapplied (initially zero, of course), by selecting sum(amount) where credit_id = the current credit transaction you're processing, subtracted from the total amount of the credit.

Then find the oldest debit with a sum(amount) in the map table that is less than the amount of the debit transaction, or that has no entries at all.

Apply as much of the credit to that transaction as is appropriate to that transaction by creating a row in the new table cross-referencing the debit transaction with the credit transaction and the amount of credit applied against the debit.

Repeat until the credit is exhausted or there are no more debit transactions that aren't already fully funded.

Using this logic allows simple splitting of debts across credits or credits across debits, and gives you what I believe you are looking for.

Whether a debit is paid, partial, or full is answered by whether the sum of entries in the new table for that transaction is 0, less than the transaction total, or equal to the transaction total. Don't store that status on the transaction itself, but rather calculate it on demand, with a left join this table and group by debit_id.

This scheme works whether debits and credits are stored in the same table, or two different tables. Add foreign keys on the ids as appropriate.

Bonus points:

Use triggers to block deletions and updates to the junction table. Those entries represent static historical facts that can't be undone. They should be considered immutable, because otherwise you are rewriting history.

Use a trigger on insert into the junction table to prevent an insertion which, when compared to existing relevant rows in the junction table, would cause the total amount allocated to the current debit or credit to exceed the actual total amount of the debit or credit. This will stop an application bug from making potentially costly math errors.

Matching debit with credit per transaction in mysql or with better algothrim?

2 Answers2