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
Solved! Go to Solution.