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 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #662050 is a reply to message #662046] Sat, 15 April 2017 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to use SUM analytic function not LAG.
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/04/2017 00:00:00          10          0           0          10
       101 13/04/2017 00:00:00          10       1000           0        1010
       102 01/04/2017 00:00:00          30          0           0          30
       102 13/04/2017 00:00:00          30       1000           0        1030
       102 14/04/2017 00:00:00        1030          0         125         905
(Assuming the first row of each item_id is the opening one with no inward nor outward and the other rows have opening quantity set to 0.)
Your example does not show how id and spl_id come into play so I ignored them.

Re: Help required in SQL Analytic Function lag() [message #662063 is a reply to message #662050] Sun, 16 April 2017 23:38 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Michel, its worked perfectly for me. Actually I started with wrong approach at the first (should start with sum rather then lag).

Thanks & Regards

Jimit
Re: Help required in SQL Analytic Function lag() [message #662068 is a reply to message #662063] Mon, 17 April 2017 06:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Help required in SQL Analytic Function lag() [message #662079 is a reply to message #662068] Mon, 17 April 2017 12:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe that's where ID comes into play. Wink

Previous Topic: Partition tablespace name does not change after moving subpartition
Next Topic: pl/sql procedure using cursors
Goto Forum:
  


Current Time: Fri Mar 29 07:38:57 CDT 2024