MySQL: UNION query to get a line for each date

MySQL

Table1
DATE        SKU     QTY
2015-06-20      ABCD    3
2015-06-20      ABCD    1
2015-06-21      ABCD    5
2015-06-23      ABCD    2
2015-06-20      EFGH    1
2015-06-21      EFGH    3

Table2
DATE        SKU     QTY
2015-06-21      ABCD    -1
2015-06-22      ABCD    -4
2015-06-23      ABCD    1
2015-06-20      EFGH    -2
2015-06-20      EFGH    1
2015-06-21      EFGH    2
2015-06-23      EFGH    3

Table3
SKU         CATEGORY
ABCD        cat1
EFGH        cat2

I want to know how many pieces I have, basically a daily running total per SKU – like the following:

DATE        SKU     CATEGORY    QTY
2015-06-20      ABCD    cat1            4   (=[0]+3+1)
2015-06-21      ABCD    cat1            8   (=[4]+5-1)
2015-06-22      ABCD    cat1            4   (=[8]-4)
2015-06-23      ABCD    cat1            7   (=[4]+2+1)
2015-06-20      EFGH    cat2            0   (=[0]+1-2+1)
2015-06-21      EFGH    cat2            5   (=[0]+3+2)
2015-06-22      EFGH    cat2            5   (=[5])
2015-06-23      EFGH    cat2            8   (=[5]+3)

basically:
– QTY of item = QTY of item of day before + sum of qty in both tables
– I need a line for all days – even if there is no lines for that day/sku in table1 and table2

I have tried:

SET @lastItem := 0, @lastValue := 0;
SELECT t6.date, t6.SKU, t6.Category, t6.qty FROM
(
SELECT
    t1.date as date, 
    t1.sku as sku,
    t3.category as Category,
    @lastValue :=  if( @lastItem = t1.sku, if(@lastValue + ifnull(t1.qty,0)<0,0,@lastValue + ifnull(t1.qty,0)), ifnull(t1.qty,0) ) as qty,
    @lastItem := t1.sku
    FROM
    (
        select date, sku, ifnull(sum(qty),0) as qty FROM(
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table1
                GROUP BY sku, date(date)
            )
            UNION
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table2
                GROUP BY sku, date(date)
            )
        ) t5
        group by date,sky
    ) t1
    LEFT JOIN table3 t3 on t1.sku = t3.sku
    order by t1.sku, t1.date
) t6

but that does not give me the a line if there is no entry in both table1 and table2

EDIT: Replaced Table1 on second table INSERT.

DDLs:

    CREATE TABLE `Table1` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`sku`,`date`,`qty`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table1`(date, sku, qty)VALUES
    ('2015-06-20', 'ABCD', '1'),
    ('2015-06-20', 'ABCD', '3'),
    ('2015-06-21', 'ABCD', '5'),
    ('2015-06-23', 'ABCD', '2'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '3');


    CREATE TABLE `Table2` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`qty`,`sku`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table2`(date, sku, qty)VALUES
    ('2015-06-22', 'ABCD', '-4'),
    ('2015-06-20', 'EFGH', '-2'),
    ('2015-06-21', 'ABCD', '-1'),
    ('2015-06-23', 'ABCD', '1'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '2'),
    ('2015-06-23', 'EFGH', '3');


    CREATE TABLE `Table3` (
      `sku` varchar(5) NOT NULL,
      `CATEGORY` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`sku`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table3`(sku, CATEGORY)VALUES
    ('ABCD', 'cat1'),
    ('EFGH', 'cat2');

Best Answer

I have the following solution, which gives the desired result, except the values for the days that there is no entries for:

set @total=0;
set @sku_name='';
SELECT date, sku, total, CATEGORY FROM
   ( 
    SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date 
        ) summary_table 
    ) final_table 
    INNER JOIN table3 USING (sku) ORDER BY sku, date;

Result:

+------------+------+-------+----------+
| date       | sku  | total | CATEGORY |
+------------+------+-------+----------+
| 2015-06-20 | ABCD |     4 | cat1     |
| 2015-06-21 | ABCD |     8 | cat1     |
| 2015-06-22 | ABCD |     4 | cat1     |
| 2015-06-23 | ABCD |     7 | cat1     |
| 2015-06-20 | EFGH |     0 | cat2     |
| 2015-06-21 | EFGH |     5 | cat2     |
| 2015-06-23 | EFGH |     8 | cat2     |
+------------+------+-------+----------+

I would give credits to @RolandoMySQLDBA who answered my question, where I get the algorithm from.

EDIT: Introduced new variable old_total

If you want to show the rows for all dates, assuming that first date in the min among all dates in both table, and the max date in the max among all dates in both tables, you can use this method:

set @total=0;
set @old_total=0;
set @sku_name='';

create temporary table tmp_table as 
    select distinct date, sku from 
        (select date from table1 union select date from table2) u1, 
        (select sku from table1 union select sku from table2) u2 
    order by sku, date;

SELECT tmp_table.date, tmp_table.sku, ifnull(total, @old_total), CATEGORY FROM
tmp_table left join
   ( 
    SELECT date, sku, @old_total:=@total, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date 
        ) summary_table 
    ) final_table using (date, sku)
    INNER JOIN table3 USING (sku) ORDER BY sku, date

The result:

+------------+------+---------------------------+----------+
| date       | sku  | ifnull(total, @old_total) | CATEGORY |
+------------+------+---------------------------+----------+
| 2015-06-20 | ABCD |                         4 | cat1     |
| 2015-06-21 | ABCD |                         8 | cat1     |
| 2015-06-22 | ABCD |                         4 | cat1     |
| 2015-06-23 | ABCD |                         7 | cat1     |
| 2015-06-20 | EFGH |                         0 | cat2     |
| 2015-06-21 | EFGH |                         5 | cat2     |
| 2015-06-22 | EFGH |                         5 | cat2     |
| 2015-06-23 | EFGH |                         8 | cat2     |
+------------+------+---------------------------+----------+
Related Question