OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » LAG function (10g, win8.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

LAG function [message #652752]

Sat, 18 June 2016 00:49

rzkhan
Messages: 370
Registered: March 2005

Senior Member

Below is my query. I just tried to use LAG function to show running balance.

The result is not as intended. I just wanted to get
sqft_production-sqft_sale+(previous row balance) using LAG.

Any help will be appreciated.


SQL> ed
Wrote file afiedt.buf

  1  select month_number, the_month,sqft_production,sqft_sale,
  2  lag(nvl(sqft_production,0)-nvl(sqft_sale,0),1,0) over(order by month_number) as bal
  3  from saad
  4* order by month_number
SQL> /

MONTH_NUMBER THE_MONT SQFT_PRODUCTION SQFT_SALE       BAL
------------ -------- --------------- --------- ---------
           1 Aug 2014           16631                   0
           2 Sep 2014          599414    573864     16631
           3 Oct 2014          493581    525998     25550
           4 Nov 2014          642554    571417    -32417
           5 Dec 2014          653926    692377     71137
           6 Jan 2015          588466    757597    -38451
           7 Feb 2015          595535    311528   -169131
           8 Mar 2015          653674    360825    284007
           9 Apr 2015          617996    818358    292849
          10 May 2015          627328    422842   -200362
          11 Jun 2015        625508.5  809673.5    204486
          12 Jul 2015        522446.5  571014.5   -184165
          13 Aug 2015        644957.5  477022.5    -48568
          14 Sep 2015        482917.5  651328.5    167935
          15 Oct 2015          668497  666105.5   -168411
          16 Nov 2015          642742    563123    2391.5
          17 Dec 2015          661168  582008.5     79619
          18 Jan 2016          643300    586037   79159.5
          19 Feb 2016        651304.5    694083     57263
          20 Mar 2016        703416.5    573785  -42778.5
          21 Apr 2016        662511.5  721153.5  129631.5
          22 May 2016        697100.5    616301    -58642
          23 Jun 2016          335832    311884   80799.5

23 rows selected.

SQL>

Report message to a moderator

Re: LAG function [message #652755 is a reply to message #652752]

Sat, 18 June 2016 01:07

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to use SUM function in its analytic form to get the running balance.

Report message to a moderator

Re: LAG function [message #652759 is a reply to message #652755]

Sat, 18 June 2016 02:16

rzkhan
Messages: 370
Registered: March 2005

Senior Member

without using LAG , cumulative sum is as under

SQL> ed
Wrote file afiedt.buf

  1  select month_number, the_month,sqft_production,sqft_sale,
  2  sum(nvl(sqft_production,0)-nvl(sqft_sale,0))
  3  over
  4  (order by month_number) as bal
  5  from saad
  6* order by month_number
SQL> /

MONTH_NUMBER THE_MONT SQFT_PRODUCTION SQFT_SALE       BAL
------------ -------- --------------- --------- ---------
           1 Aug 2014           16631               16631
           2 Sep 2014          599414    573864     42181
           3 Oct 2014          493581    525998      9764
           4 Nov 2014          642554    571417     80901
           5 Dec 2014          653926    692377     42450
           6 Jan 2015          588466    757597   -126681
           7 Feb 2015          595535    311528    157326
           8 Mar 2015          653674    360825    450175
           9 Apr 2015          617996    818358    249813
          10 May 2015          627328    422842    454299
          11 Jun 2015        625508.5  809673.5    270134
          12 Jul 2015        522446.5  571014.5    221566
          13 Aug 2015        644957.5  477022.5    389501
          14 Sep 2015        482917.5  651328.5    221090
          15 Oct 2015          668497  666105.5  223481.5
          16 Nov 2015          642742    563123  303100.5
          17 Dec 2015          661168  582008.5    382260
          18 Jan 2016          643300    586037    439523
          19 Feb 2016        651304.5    694083  396744.5
          20 Mar 2016        703416.5    573785    526376
          21 Apr 2016        662511.5  721153.5    467734
          22 May 2016        697100.5    616301  548533.5
          23 Jun 2016          335832    311884  572481.5

23 rows selected.

SQL>

But I just wanted to do it with LAG.

Report message to a moderator

Re: LAG function [message #652760 is a reply to message #652759]

Sat, 18 June 2016 02:30

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can't do it with LAG.

Report message to a moderator

Previous Topic:	connect and level
Next Topic:	Exception Handling

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 06:54:31 CDT 2024