asked on

Transactional database

claimid lossreserve losspaid RunDate
3456 100.00 450.00 2012/12/01
3457 200.00 150.00 2012/12/01
3458 239.00 2000.00 2012/12/01
3459 20.00 50.00 2012/12/01
3456 300.00 156.00 2012/12/02
3457 654.00 456.00 2012/12/02
3458 900.00 213.00 2012/12/02
3459 12.00 34.00 2012/12/02
3461 145.00 29.00 2012/12/02
3456 245.00 98.00 2012/12/03
3457 345.00 987.00 2012/12/03
3458 345.00 567.00 2012/12/03
3459 234.45 123.00 2012/12/03

I need to perform transactional calculation using the current and previous rundate to determine the lossreserve and losspaid.

Jared_S

Hi Olungus,

Do you ever a claimID process more than once in a single day?

Will you ever have a claimID that doesn't have a previous run date?

And just to confirm, loss reserve and loss paid for the last two run dates are just totals of two columns, respectively (?)

hforhirenpatel

I hope this will help to u.

select sum(lossreserve),sum(losspaid) from table_name where RunDate<date(now()) and RunDate>date_sub(date(now()), interval 2 day);

olongus

ASKER

The issue here is that the claims data comes in everyday and the data that is presented is asof that day. I need to derive transactional information from the data that is why I've appended the data when the data was loaded in order to perform the calculation using the rundate. If a claim occurred yesterday, I want to compare the same claim today to derive what the difference between the two values.
This is what I have so far.

;With cte as
(
Select [Claim#] AS ClaimNumber,(([Indeminity Incurred]+ [Medical Incurred]+ [Property Damage Incurred])
-([Indeminity Paid]+ [Medical Paid]+ [Property Damage Paid])) AS [Loss Reserve]
,([All Incurred])-([All Paid]) AS [ALE Res A&O]
,[TotalInc],[TotalPaid],[TotalPaidWithinGivenDateRange]
,[Outstanding],[Net Incurred (TotalPaid + Outstanding - RecoveryPaid)]
,EffDate
,ExpDate
,RunDate
,ROW_NUMBER ()Over (Partition by Claim# Order by Rundate) as Rownumber2
from #XXXXXXXXXXXXXXXXXXXXX
)
Select cur.Rownumber2,Cur.ClaimNumber,isnull(cur.[Loss Reserve],0.00) as LossReserve,cur.[ALE Res A&O],isnull(cur.[ALE Res A&O],0.00) as [ALE A&O Reserve]
,Isnull(prv.[Loss Reserve],0.00) as PreviousLossReserve
into #ss
From cte Cur Left Join cte Prv
On cur.ClaimNumber=prv.ClaimNumber
and Cur.Rownumber2=Prv.RowNumber2+1

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial