Let's say I have the following Inventory table.
id item_id stock_amount Date
(1, 1, 10, '2020-01-01T00:00:00')
(2, 1, 9, '2020-01-02T00:00:00')
(3, 1, 8, '2020-01-02T10:00:00')
(4, 3, 11, '2020-01-03T00:00:00')
(5, 3, 13, '2020-01-04T00:00:00')
(6, 4, 7, '2020-01-05T00:00:00')
(7, 2, 12, '2020-01-06T00:00:00')
Basically, per each day, I want to get the sum of stock_amount for each unique item_id but it should exclude the current day's stock amount. The item_id chosen should be the latest one. This is to calculate the starting stock on each day. So the response in this case would be:
Date starting_amount
'2020-01-01T00:00:00' 0
'2020-01-02T00:00:00' 10
'2020-01-03T00:00:00' 8
'2020-01-04T00:00:00' 19 -- # -> 11 + 8 (id 5 + id 3)
'2020-01-05T00:00:00' 21 -- # -> 13 + 8
'2020-01-06T00:00:00' 28 -- # -> 7 + 13 + 8
Any help would be greatly appreciated.
Using nested subqueries like this:
select
Date,
coalesce(sum(stock_amount), 0) starting_amount
from
(
select
row_number() over(partition by i1.Date, item_id order by i2.Date desc) i,
i1.Date,
i2.item_id,
i2.stock_amount
from
(select distinct date_trunc('day', Date) as Date from Inventory) i1
left outer join
Inventory i2
on i2.Date < i1.Date
) s
where i = 1
group by Date
order by Date
This query sorts in descending order and uses the first row.