I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast.
ei= ei(previous row) + receipts - forecast
wk bi r f ei
1 100 20 80 40
2 50 0
3 30 15 15
4 40 14 41
5 10 31
create table ra_analytics.Test_1Test (wk int, bi int ,r int ,f int ,ei int);
insert into ra_analytics.Test_1Test values (1, 100 ,20 ,80 ,0);
insert into ra_analytics.Test_1Test values (2, 0 ,0 ,50 ,0);
insert into ra_analytics.Test_1Test values (3, 0 ,30 ,15 ,0);
insert into ra_analytics.Test_1Test values (4, 0 ,40 ,14 ,0);
insert into ra_analytics.Test_1Test values (5, 0 ,0 ,10 ,0);
select * from ra_analytics.test_1test order by wk
--Update first week of inventory
update ra_analytics.test_1test
set Ei= bi+r-f
from ra_analytics.test_1test where wk=1
--for next weeks inventory using lag function
select wk, bi, r, f, lag(ei,1) over(order by wk) +r -f as endinv
from ra_analytics.test_1test
order by wk
Best Answer
You need recursive common table expresion , feature not available in redshift , try another server for this or use a loop . Lag is not referring computed columns is not helpful in this case
On another sql server it should look like this :