I am using oraclexe and also PostgreSQL and require following data set (sql query) (to get inventory closing qty and amount at particular date) when running this query with
conditions between idate '03-07-2021' to '04-07-2021',
value for ref with 'OT#' wist to get with negative qty and amt.
bal qty and amt before condition idate i.e (240,10000 in this example) want to be in the beginning of the result set.
CREATE TABLE myinv ( loc varchar(20) ,item varchar2(50) ,idate date ,ref1 varchar2(20) ,rt number(10,1),qty number(10,2), amt number(10,2));
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) values ('A', 'IT1', to_Date('2021-07-01','yyyy-mm-dd'), 'IN5', 40, 160, 6400)
/
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('A', 'IT1', to_Date('2021-07-02','yyyy-mm-dd'), 'IN6', 45, 80, 3600)
/
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-03','yyyy-mm-dd'), 'OT2', 41.67, 80, 3333.6)
/
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('A', 'IT1', to_Date('2021-07-04', 'yyyy-mm-dd'), 'IN7', 35, 40, 1400)
/
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-04','yyyy-mm-dd'), 'OT3', 40.33, 100, 4033)
/
INSERT INTO myinv (loc, item, idate, ref1, rt, qty, amt) VALUES('', 'IT1', to_date('2021-07-04','yyyy-mm-dd'), 'OT4', 40.33, 40, 1613.2)
/
i have try following query but unable to get result as shown above in "REQUIRED DATA SET"
SELECT item, idate,ref1, rt,
lag(cast(closing_qty as int),1,0) over (partition by item order by idate) opening_qty,
qty,
closing_qty,
lag(cast(closing_amt as int),1,0) over (partition by item order by idate) opening_amt,
amt
FROM
(
select
item, idate,ref1, rt,qty,amt,
sum(qty) over (partition by item order by idate) closing_qty,
sum(amt) over (partition by item order by idate) closing_amt
from
myinv where idate > to_date('20210702', 'yyyymmdd')
) t