Home » SQL & PL/SQL » SQL & PL/SQL » Help required in SQL Analytic Function lag() (Oracle 11gR2 11.2.0.1.0)
Help required in SQL Analytic Function lag() [message #662046] |
Sat, 15 April 2017 07:24 |
jimit_shaili
Messages: 237 Registered: June 2006 Location: India, Ahmedabad
|
Senior Member |
|
|
Dear All,
Here I've a case wherein I required stock ledger kind of report from the query.
CREATE TABLE DSP.TEMP
(
ID NUMBER(8),
SPL_ID NUMBER(8),
ITEM_ID NUMBER(8),
TRAN_DATE DATE,
OPENING_QTY NUMBER(10),
INWARD_QTY NUMBER(10),
OUTWARD_QTY NUMBER(10)
);
Insert into TEMP (ID, SPL_ID, ITEM_ID, TRAN_DATE, OPENING_QTY, INWARD_QTY, OUTWARD_QTY) Values (1, 1, 101, TO_DATE('04/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 0, 0);
Insert into TEMP (ID, SPL_ID, ITEM_ID, TRAN_DATE, OPENING_QTY, INWARD_QTY, OUTWARD_QTY) Values (2, 1, 101, TO_DATE('04/13/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 1000, 0);
Insert into TEMP (ID, SPL_ID, ITEM_ID, TRAN_DATE, OPENING_QTY, INWARD_QTY, OUTWARD_QTY) Values (3, 1, 102, TO_DATE('04/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30, 0, 0);
Insert into TEMP (ID, SPL_ID, ITEM_ID, TRAN_DATE, OPENING_QTY, INWARD_QTY, OUTWARD_QTY) Values (4, 1, 102, TO_DATE('04/13/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 1000, 0);
Insert into TEMP (ID, SPL_ID, ITEM_ID, TRAN_DATE, OPENING_QTY, INWARD_QTY, OUTWARD_QTY) Values (5, 1, 102, TO_DATE('04/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 0, 125);
commit;
Below is my try to get desired result.
select spl_id,item_id,tran_date
,case
when tran_date = '01-apr-2017'
then opening_qty
else 0
end as opening_qty
,inward_qty,outward_qty
,case
when tran_date = '01-apr-2017'
then ((opening_qty + inward_qty) - outward_qty)
else (((lag((opening_qty + inward_qty) - outward_qty) over(partition by spl_id,item_id order by spl_id,item_id,tran_date))+inward_qty)-outward_qty)
end as closing_qty
from temp
order by spl_id,item_id,tran_date;
SPL_ID ITEM_ID TRAN_DATE OPENING_QTY INWARD_QTY OUTWARD_QTY CLOSING_QTY
------- ---------- --------- ----------- ---------- ----------- -----------
1 101 01-APR-17 10 0 0 10
1 101 13-APR-17 0 1000 0 1010
1 102 01-APR-17 30 0 0 30
1 102 13-APR-17 0 1000 0 1030
1 102 14-APR-17 0 0 125 875
In my try, opening and closing are carried forwarded as I required. I would like to have result like below.
SPL_ID ITEM_ID TRAN_DATE OPENING_QTY INWARD_QTY OUTWARD_QTY CLOSING_QTY
------- ---------- --------- ----------- ---------- ----------- -----------
1 101 01-APR-17 10 0 0 10
1 101 13-APR-17 10 1000 0 1010
1 102 01-APR-17 30 0 0 30
1 102 13-APR-17 30 1000 0 1030
1 102 14-APR-17 1030 0 125 905
Please help me out in my try or suggest the way to resolve it.
Regards
Jimit
|
|
|
Re: Help required in SQL Analytic Function lag() [message #662049 is a reply to message #662046] |
Sat, 15 April 2017 08:22 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
select spl_id,
item_id,
opening_qty,
inward_qty,
outward_qty,
sum(opening_qty + inward_qty - outward_qty)
over(
partition by spl_id,item_id
order by id
) closing_qty
from temp
order by id
/
SPL_ID ITEM_ID OPENING_QTY INWARD_QTY OUTWARD_QTY CLOSING_QTY
---------- ---------- ----------- ---------- ----------- -----------
1 101 10 0 0 10
1 101 0 1000 0 1010
1 102 30 0 0 30
1 102 0 1000 0 1030
1 102 0 0 125 905
SQL>
SY.
|
|
|
|
|
Re: Help required in SQL Analytic Function lag() [message #662068 is a reply to message #662063] |
Mon, 17 April 2017 06:02 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Keep in mind, Michel's solution can produce results you didn't expect if multiple transactions on same item are possible for same day. Compare:
SQL> select *
2 from temp
3 /
ID SPL_ID ITEM_ID TRAN_DATE OPENING_QTY INWARD_QTY OUTWARD_QTY
---------- ---------- ---------- --------- ----------- ---------- -----------
1 1 101 01-APR-17 10 0 0
2 1 101 13-APR-17 0 1000 0
3 1 101 13-APR-17 0 500 0
4 1 102 01-APR-17 30 0 0
5 1 102 13-APR-17 0 1000 0
6 1 102 14-APR-17 0 0 125
6 rows selected.
SQL> select spl_id,
2 item_id,
3 opening_qty,
4 inward_qty,
5 outward_qty,
6 sum(opening_qty + inward_qty - outward_qty)
7 over(
8 partition by spl_id,item_id
9 order by id
10 ) closing_qty
11 from temp
12 order by id
13 /
SPL_ID ITEM_ID OPENING_QTY INWARD_QTY OUTWARD_QTY CLOSING_QTY
---------- ---------- ----------- ---------- ----------- -----------
1 101 10 0 0 10
1 101 0 1000 0 1010
1 101 0 500 0 1510
1 102 30 0 0 30
1 102 0 1000 0 1030
1 102 0 0 125 905
6 rows selected.
SQL> select ITEM_ID, TRAN_DATE,
2 sum(OPENING_QTY+INWARD_QTY-OUTWARD_QTY)
3 over (partition by item_id order by tran_date)
4 - (INWARD_QTY-OUTWARD_QTY)
5 OPENING_QTY,
6 INWARD_QTY, OUTWARD_QTY,
7 sum(OPENING_QTY+INWARD_QTY-OUTWARD_QTY)
8 over (partition by item_id order by tran_date)
9 CLOSING_QTY
10 from temp
11 order by item_id, tran_date
12 /
ITEM_ID TRAN_DATE OPENING_QTY INWARD_QTY OUTWARD_QTY CLOSING_QTY
---------- --------- ----------- ---------- ----------- -----------
101 01-APR-17 10 0 0 10
101 13-APR-17 510 1000 0 1510
101 13-APR-17 1010 500 0 1510
102 01-APR-17 30 0 0 30
102 13-APR-17 30 1000 0 1030
102 14-APR-17 1030 0 125 905
6 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 23:54:33 CDT 2024
|