Results 1 to 3 of 3
  1. #1
    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. #2
    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. #3
    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
  •