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');
StoredProcedure
to generate this information in oneTEMPORARY TABLE
or normalTABLE
?