2

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
0

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 :

with cter as (
select r,f , wk , bi+r-f as endinv from test_1test where wk = 1 
union all 
select t.r, t.f , t.wk, c.endinv + t.r -t.f as endinv from test_1test t inner join cter c on t.wk -1  = c.wk 
)
select * from cter order by wk 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.