# Thread: Calculating reserve from initial reserve

1. Registered User
Join Date
Jun 2012
Posts
2

## Unanswered: Calculating reserve from initial reserve

I need to calculate changes in reserve from the initial reserve calculation as seen in the diagram below. The initial reserve is derived from 1000-100 = 900. Next 900-50 = 850, 850-150 = 700 and so on. I would really appreciate it if someone can assist me. Thanks

Before:
Claimid totalincur payment reserve Rownumber
1234 1000 100 900 1
1234 1000 50 0 2
1234 1000 150 0 3
1234 1000 150 0 4
1235 2000 1000 1000 1
1235 2000 100 0 2
1235 2000 150 0 3

After:
Claimid totalincur payment reserves Rownumber
1234 1000 100 900 1
1234 1000 50 850 2
1234 1000 150 700 3
1234 1000 150 550 4
1235 2000 1000 1000 1
1235 2000 100 900 2
1235 2000 150 750 3

2. Registered User
Join Date
Apr 2012
Posts
213
Try:

Code:
```with CTE as
(
select *
from MyTable
where Rownumber = 1

union all

select t.Claimid, t.totalincur, t.payment, c.reserve - t.payment, t.Rownumber
from MyTable as t
join CTE as c on (c.Claimid = t.Claimid) and (c.Rownumber = t.Rownumber - 1)
)

update MyTable
set reserve = c.reserve
from MyTable as t
join CTE as c on (c.Claimid = t.Claimid) and (c.Rownumber = t.Rownumber)

select * from MyTable
order by Claimid, Rownumber```
Hope this helps.

3. Registered User
Join Date
Jun 2012
Posts
2
Thanks Imex, this was really helpful....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•