Skip to Main Content

closing and opening balance SQL

AQHJul 6 2021

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.
image.png

CREATE TABLE myinv ( loc varchar2 ,item varchar2 ,idate date ,"ref" varchar2 ,rt number NULL,qty number, amt number);

INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('A', 'IT1', '2021-07-01', 'IN5', 40, 160, 6400);
INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('A', 'IT1', '2021-07-02', 'IN6', 45, 80, 3600);
INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('', 'IT1', '2021-07-03', 'OT2', 41.67, 80, 3333.6);
INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('A', 'IT1', '2021-07-04', 'IN7', 35, 40, 1400);
INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('', 'IT1', '2021-07-04', 'OT3', 40.33, 100, 4033);
INSERT INTO myinv values (loc, item, idate, "ref", rt, qty, amt) VALUES('', 'IT1', '2021-07-04', '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,ref, 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,ref, 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
) t

Comments
Post Details
Added on Jul 6 2021
0 comments
23 views