3
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');
  • 1
    So, in simple words, you want to know how many pieces you have, basically a daily running total per SKU, right? – dezso Jun 29 '15 at 18:58
  • @dezso. yes. you explain it way better than I did :) – Missing GoldFish Jun 29 '15 at 19:21
  • Could you please give us DDL (CREATE TABLE blah (....)) and also the DML to fill them - this will enable those who wish to help you to construct the tables without having to do it "by hand". – Vérace Jun 29 '15 at 19:22
  • Will you use a StoredProcedure to generate this information in one TEMPORARY TABLE or normal TABLE? – oNare Jun 29 '15 at 22:36
  • @oNare I'd rather go for a temporary table but all options are possible – Missing GoldFish Jun 30 '15 at 1:17
1

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     |
+------------+------+---------------------------+----------+
  • thank you. my query is longer but return the same. I am now only missing the days that there is no entries for. any idea? – Missing GoldFish Jun 30 '15 at 16:54
  • I updated the answer to include dates where there is no transactions – Jehad Keriaki Jun 30 '15 at 18:00
  • this looks great. the only change would be to get 5 for date:2015-06-22 and SKU:EFGH – Missing GoldFish Jun 30 '15 at 21:36
  • Voila.. it is fixed by introducing a new variable to hold the old value if it didn't exist in the 'left' table – Jehad Keriaki Jul 1 '15 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.