Running total by item and week

Question

I am trying to determine what my inventory level for each product is at the end of each business week.

I have a table that records all product transactions

+------------+-------------+------------+------------+
| ProductID  | Quantity    | Created_at |Cause       |
+------------+-------------+------------+------------+
|          1 | 200         | 2015-06-01 |Delivery    |
|          1 | -2          | 2015-06-02 |Order       |
|          1 | -1          | 2015-06-12 |Order       |
|          2 | 45          | 2015-06-15 |Delivery    |
|          2 | -5          | 2015-06-16 |Order       |
|          2 | -1          | 2015-06-17 |Broken      |
|          1 | 100         | 2015-06-21 |Delivery    |
+------------+-------------+------------+------------+

I would need to sum the quantity only up to the particular date, by product, to show something like this

+------------+-------------+------------+
| ProductID  | Quantity    | Week       |
+------------+-------------+------------+
|          1 | 198         | 2015-06-05 |
|          1 | 197         | 2015-06-12 |
|          1 | 197         | 2015-06-19 |
|          2 | 39          | 2015-06-19 |
|          1 | 297         | 2015-06-26 |
|          2 | 39          | 2015-06-26 |
+------------+-------------+------------+

I have tried various combinations of With Rollup and @runtot:= But have not been successful so far.

for the week you need a date or a week number will be enough ? — Daniel E., Mar 07 '18 at 16:45
A week number would be fine as well. There just needs to be something that identifies and groups by each week. — Matty, Mar 07 '18 at 16:58

score 2 · Accepted Answer · answered Mar 07 '18 at 17:24

So, something like:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(product_id INT NOT NULL
,created_at DATE NOT NULL 
,quantity INT NOT NULL
,cause  VARCHAR(20) NOT NULL
,PRIMARY KEY(product_id,created_at)
);

INSERT INTO my_table VALUES
(1,'2015-06-01',200,'Delivery'),
(1,'2015-06-02', -2,'Order'),
(1,'2015-06-12', -1,'Order'),
(2,'2015-06-15', 45,'Delivery'),
(2,'2015-06-16', -5,'Order'),
(2,'2015-06-17', -1,'Broken'),
(1,'2015-06-21',100,'Delivery');

SELECT x.*
     , CASE WHEN @prev = product_id THEN @i:=@i+total ELSE @i:=total END running
     , @prev:=product_id prev 
  FROM 
     ( SELECT product_id
            , YEARWEEK(created_at) yw
            , SUM(quantity) total 
         FROM my_table
        GROUP 
           BY product_id
            , yw
     ) x
     , (SELECT @prev:=null,@i:=0) vars 
 ORDER 
    BY product_id
     , yw;
+------------+--------+-------+---------+------+
| product_id | yw     | total | running | prev |
+------------+--------+-------+---------+------+
|          1 | 201522 |   198 |     198 |    1 |
|          1 | 201523 |    -1 |     197 |    1 |
|          1 | 201525 |   100 |     297 |    1 |
|          2 | 201524 |    39 |      39 |    2 |
+------------+--------+-------+---------+------+

This is brilliant. I am not familiar at all with setting variables. I will need to study this more! Thanks! — Matty, Mar 07 '18 at 17:35

Running total by item and week

1 Answers1