CODEWITHSUNDEEP
mysql
TommyBoy
TommyBoy

Reputation: 1

MySQL union on two tables with cumulative value aka "bank statement"

I'd really appreciate your help.

The situation is I have these two tables:

Table 1: debits. Example:

    date       item   value_debits
    2012-08-01 item1  10
    2012-08-03 item2  15

Table 2: credits. Example:

    date       item   value_credits
    2012-07-31 item3  20
    2012-08-02 item4  30

Desired result:

    date       item   value  balance
    2012-07-31 item3  20     20
    2012-08-01 item1  (10)   10
    2012-08-02 item4  30     40
    2012-08-03 item3  (15)   25

I can easily calculate cumulative values for each of the tables separately:

set @cumulative :=0;
select date, item, value_debits, @cumulative := @cumulative + value_debits AS "Cumulated" 
from debits
order by date DESC

It's not too difficult to union and order by date these two tables to get this: 

    date       item   value
    2012-07-31 item3  20
    2012-08-01 item1  10
    2012-08-02 item4  30
    2012-08-03 item3  15

But how to get to the desired result is beyond me.

Thanks in advance!

Upvotes: 0

Views: 231

Answers (2)

Holger Brandt
Holger Brandt

Reputation: 4354

Here is my admittedly inefficient try at your solution. If time presents, I will try to work out a more efficient one. However, it does work.

SELECT U.`date`, U.item, U.value, 
  (SELECT SUM(U2.value) FROM 
     (SELECT `date`,item,-value_debits as value
      FROM debits
      UNION ALL
      SELECT `date`,item,value_credits as value
      FROM credits
     ) AS U2
   WHERE `date` <= U.date) 
   AS balance 
FROM 
  (SELECT `date`,item,-value_debits as value
   FROM debits
   UNION ALL
   SELECT `date`,item,value_credits as value
   FROM credits
  ) AS U
ORDER BY `date`

This would have been slightly easier if the credit and debits were in the same table.

BETTER SOLUTION

SET @total=0;
SELECT U.`date`, 
       U.item, 
       CASE WHEN U.value<0 
          THEN CONCAT('(', -U.value, ')') 
          ELSE U.value
       END as value, 
       @total:=@total+value AS balance
FROM (SELECT `date`,item,-value_debits as value
      FROM debits
      UNION ALL
      SELECT `date`,item,value_credits as value
      FROM credits
     ) AS U
ORDER BY U.`date`;

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

You can use:

SELECT     a.date,
           a.item,
           CASE 
               WHEN a.value < 0 THEN CONCAT('(', a.value, ')') 
               ELSE a.value
           END AS value,
           @bal:=@bal+a.value AS balance
FROM       (
           SELECT date, item, value_debits*-1 AS value FROM debits
           UNION ALL
           SELECT date, item, value_credits FROM credits
           ) a
CROSS JOIN (SELECT @bal:=0) bal_init
ORDER BY   a.date

Take a look at this SQLFiddle Demo

Upvotes: 1

Related Questions