Joseph Goh Joseph Goh - 7 months ago 8
SQL Question

MySQL Summary in new column

I have 2 tables which consist a set of data as below and i want to get the result as in the RESULT which will have a calculation in field balance:

I am stuck on the balance field, how do i get the balance running?

tblIn

in_date | code | in_qty
-----------|---------|---------
2016-04-01 | aaa | 100
2016-04-02 | aaa | 100
2016-04-03 | aaa | 200
2016-04-06 | aaa | 400


tblOut

out_date | code | out_qty
-----------|---------|---------
2016-04-02 | aaa | 100
2016-04-08 | aaa | 400


RESULT

date | code | in_qty | out_qty | balance
-----------|---------|----------|----------|---------
2016-04-01 | aaa | 100 | 0 | 100
2016-04-02 | aaa | 100 | 0 | 200
2016-04-02 | aaa | 0 | 100 | 100
2016-04-03 | aaa | 200 | 0 | 300
2016-04-06 | aaa | 400 | 0 | 700
2016-04-08 | aaa | 0 | 400 | 300


QUERY (thanks to @1000111)

SELECT
t.*,
@prevBalance := (t.in_qty - t.out_qty) + IFNULL(@prevBalance,0) AS balance
FROM
(
SELECT
in_date date,
code,
in_qty,
0 AS out_qty
FROM tblin

UNION

SELECT
out_date,
code,
0,
out_qty
FROM tblout
) t , (SELECT @prevBalance := NULL) var
ORDER BY t.date;


This query will get the result as in RESULT, but what if i want this kind of result. MySQL will summary upto 2016-04-03 as single line and continue with detail for the next date.

RESULT1

date | code | in_qty | out_qty | balance
-----------|---------|----------|----------|---------
2016-04-03 | aaa | 0 | 0 | 300
2016-04-06 | aaa | 400 | 0 | 700
2016-04-08 | aaa | 0 | 400 | 300

Answer

try this query.. i tested it with 2016-04-02 but feel free to change the one line that is commented to change date to any date you like

SELECT @startDate date,
       -- all this below to determine code since code has to come from somewhere
       -- for now we'll just select code of the latest date that is before
       -- or equal to @startDate
       (SELECT TCode.code FROM
          (SELECT code,in_date FROM tblin   WHERE in_date <= @startDate
           UNION ALL
           SELECT code,out_date FROM tblout WHERE out_date <= @startDate)TCode
         ORDER BY TCode.in_date DESC
        LIMIT 1
        ) as Code,
        0,0,
        balance 
        FROM
        (SELECT @prevbalance :=(SELECT SUM(in_qty) FROM tblin,
-- Change the date in below line to any date you desire as @startDate is used throughout this whole query
                                (SELECT @startDate := DATE('2016-04-02'))TDATE WHERE in_date <= @startDate)-
                               (SELECT SUM(out_qty) FROM tblout WHERE out_date <= @startDate) as balance)T4
UNION ALL
SELECT * FROM
  (SELECT T.*,@balance := @balance + (t.in_qty - t.out_qty) AS balance
   FROM 
    (SELECT in_date date,code,in_qty,0 AS out_qty FROM tblin WHERE in_date > @startDate
     UNION ALL
     SELECT out_date,    code,     0,     out_qty FROM tblout WHERE out_date > @startDate
    )T,(SELECT @balance:=@prevbalance)initial
   ORDER BY T.date ASC,T.in_qty DESC
  )T3;

sqlfiddle

Comments