I have a tricky situation , to know the stock balance based on receipt date on following criteria s -
1.Stock days to calculate from the first receipt date till next receipt date
2.Stock days to consider only receipt if there are no issuances.
item A--- STOCK BALANCE R-I (10- 3 + 5 = 12)
item B---STOCK BALANCE R-I (5-0 =5 )
item C --- STOCK BALANCE R-I (5-1-4 =1)
item D--- STOCK BALANCE R-I (5-1=4)
[code]
create table stk_trans(stk_date date,stk_item varchar2(12),stk_qty number,stk_r_i varchar2(1))
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/01/2018','DD/MM/YYYY'),'A',10,'R')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/03/2018','DD/MM/YYYY'),'A',2,'I')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/04/2018','DD/MM/YYYY'),'A',1,'I')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/06/2018','DD/MM/YYYY'),'A',5,'R')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/01/2018','DD/MM/YYYY'),'B',5,'R')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/04/2018','DD/MM/YYYY'),'C',5,'R')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/05/2018','DD/MM/YYYY'),'C',1,'I')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/05/2018','DD/MM/YYYY'),'C',4,'I')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('01/06/2018','DD/MM/YYYY'),'D',5,'R')
insert into stk_trans (stk_date ,stk_item ,stk_qty, stk_r_i) values (TO_DATE('25/06/2018','DD/MM/YYYY'),'D',1,'I')
--Need out put as below...
item 0-90 91-180 181-270 >270
A 5 7
B 5
C 1
D 4
[/code]