I need to produce following and having hard time connecting the dots. I understand that I need to use CTE but just not able to.
| id | Quantity | Unit Cost | Created at |
|---|---|---|---|
| 1 | 100 | 4 | now()+'1 hour' |
| 2 | 200 | 5 | now()+'2 hours' |
| 3 | -250 | 0 | now()+'3 hours' |
| 4 | 50 | 5 | now()+'4 hours' |
| 5 | -55 | 0 | now()+'5 hours' |
Table/DDL:
`CREATE TABLE inventory (
id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
quantity NUMERIC,
unit_cost NUMERIC(19,2)
);
INSERT INTO inventory (id, quantity, unit_cost, created_at)
VALUES
(1, 100,4, now()+'1 hour'),
(2, 200,5, now()+'2 hours'),
(3, -250,0, now()+'3 hours'),
(4, 10,3, now()+'4 hours'),
(5, -55,0, now()+'5 hours');`
Query should produce following:
| id | Quantity | FIFO Cost | Created at |
|---|---|---|---|
| 1 | 100 | 4 | now()+'1 hour' |
| 2 | 200 | 5 | now()+'2 hours' |
| 3 | 100 | -4 | now()+'3 hours' |
| 3 | 150 | -5 | now()+'3 hours' |
| 4 | 10 | 3 | now()+'4 hours' |
| 5 | 50 | -5 | now()+'5 hours' |
| 5 | 5 | -3 | now()+'5 hours' |
Basically, on negative quantities (invoice, etc) it should use units from positive quantity (purchase, etc) and use up to unused quantity and then go to the next one.
Thanks !