srini_pendy
asked on
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_balanc
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
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_balanc
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just the outer is sufficient
NVL(LAG(closing_balance, 1) OVER (PARTITION BY account_no ORDER BY account_no, year, month),
0
)