help with row between preceding 1 for balance calculation

Database

help with row between preceding 1 for balance calculation

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.

IDWEEK_END_DBalance after ActivityOutstanding balance
1234562016-12-245050
1234562016-12-31null50
1234562017-01-07nullnull
1234562017-01-14nullnull
1234562017-06-1000
1234562017-06-17null0
1234562017-06-24nullnull
9999992016-10-296060
9999992016-11-0510.3910.39
9999992016-11-1200
9999992016-11-19null0
9999992016-11-26nullnull

 

Can you please help me here.

 

Thanks

Vinay


Accepted Solutions
Junior Contributor

Re: help with row between preceding 1 for balance calculation

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)

 

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: help with row between preceding 1 for balance calculation

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)