help with row between preceding 1 for balance calculation

[vinaykbaghur]

Hi All,

Below is how the data coming to us.

ID	WEEK_END_D	Balance after Activity
123456	24Dec2016	50
123456	31Dec2016	null
123456	07Jan2017	null
123456	14Jan2017	null
123456	10Jun2017	0
123456	17Jun2017	null
123456	24Jun2017	null
999999	29Oct2016	60
999999	05Nov2016	10.39
999999	12Nov2016	0
999999	19Nov2016	null
999999	26Nov2016	null

For each ID we have balance for accounting week end date. If there is no activity for an ID in that week then it comes as null. Now I need to add additional column (outstanding balance) giving the balance like below.

ID	WEEK_END_D	Balance after Activity	Outstanding balance
123456	24Dec2016	50	50
123456	31Dec2016	null	50
123456	07Jan2017	null	50
123456	14Jan2017	null	50
123456	10Jun2017	0	0
123456	17Jun2017	null	0
123456	24Jun2017	null	0
999999	29Oct2016	60	60
999999	05Nov2016	10.39	10.39
999999	12Nov2016	0	0
999999	19Nov2016	null	0
999999	26Nov2016	null	0

SyntaxEditor Code Snippet : 

select ID,WEEK_END_D,BALANCE_AFTER_ACTIVITY,case when BALANCE_AFTER_ACTIVITY is null then min(BALANCE_AFTER_ACTIVITY) over(partition by id order by WEEK_END_D rows between 1 preceding and 1 preceding) else BALANCE_AFTER_ACTIVITY end as outstanding_balance
from TableA

 Below is the result i got but its not accurate.

ID	WEEK_END_D	Balance after Activity	Outstanding balance
123456	2016-12-24	50	50
123456	2016-12-31	null	50
123456	2017-01-07	null	null
123456	2017-01-14	null	null
123456	2017-06-10	0	0
123456	2017-06-17	null	0
123456	2017-06-24	null	null
999999	2016-10-29	60	60
999999	2016-11-05	10.39	10.39
999999	2016-11-12	0	0
999999	2016-11-19	null	0
999999	2016-11-26	null	null

Can you please help me here.

Thanks

Vinay

[dnoeth · Accepted solution]

This is a simple task for LAST_VALUE:

Last_Value(BALANCE_AFTER_ACTIVITY IGNORE NULLS)
Over(PARTITION BY id 
     ORDER BY WEEK_END_D ROWS Unbounded Preceding)