Oracle LAG Function

Hi ,
I'm using Lag function to maintain balances, however, when the year changes , the closing balance from previous year not coming up as opening balance in the current year.
Here is the SQL;

nvl(lag(nvl(closing_balance,0),1) over(partition by
ACCOUNT_NO,YEAR,MONTH order by
ACCOUNT_NO,YEAR,MONTH),0)

Please see the last line year = 2011 , Opening Balanace is 0 instead of 50

Account Number Year      Month      Opening Bal      Credit      Debit      Closing Bal
999      2010      4      0      -10      20      10
999      2010      5      10      0      0      10
999      2010      6      10      -10      50      50
999      2010      7      50      0      0      50
999      2010      8      50      0      0      50
999      2010      9      50      0      0      50
999      2010      10      50      0      0      50
999      2010      11      50      0      0      50
999      2010      12      50      0      0      50
999      2011      1      0      -10      20      10
srini_pendyAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
it's because your partition is for a single account/year/month.

Just partition by account, but keep the ordering


 NVL(
           LAG(NVL(closing_balance, 0), 1)
               OVER (PARTITION BY account_no ORDER BY account_no, year, month),
           0
       )
0
 
sdstuberCommented:
you don't need the inner NVL

just the outer is sufficient


 NVL(LAG(closing_balance, 1) OVER (PARTITION BY account_no ORDER BY account_no, year, month),
           0
       )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.