[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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.
0
olongus
Asked:
olongus
  • 2
1 Solution
 
Jared_SCommented:
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 (?)
0
 
hforhirenpatelCommented:
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);
0
 
olongusAuthor Commented:
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
0
 
Jared_SCommented:
I think your going to get a lot of use out of the rank function here.

I wrote this based off of the sample data. It probably isn't the most stream-lined way to do it, but it works.

The first half is just creating a variable table and inserting your sample data...

declare @claims table (claimid int,lossreserve money,losspaid money,RunDate datetime)

insert into @claims values (3456,             100.00,         450.00,        '2012/12/01')
insert into @claims values (3457,             200.00,         150.00,        '2012/12/01')
insert into @claims values (3458,             239.00,         2000.00,       '2012/12/01')
insert into @claims values (3459,             20.00 ,          50.00,        '2012/12/01')
insert into @claims values (3456,             300.00,         156.00,        '2012/12/02')
insert into @claims values (3457,             654.00,         456.00,        '2012/12/02')
insert into @claims values (3458,             900.00,         213.00,        '2012/12/02')
insert into @claims values (3459,             12.00,           34.00,        '2012/12/02')
insert into @claims values (3461,             145.00,         29.00,         '2012/12/02')
insert into @claims values (3456,             245.00,         98.00,         '2012/12/03')
insert into @claims values (3457,             345.00,         987.00,        '2012/12/03')
insert into @claims values (3458,             345.00,         567.00,        '2012/12/03')
insert into @claims values (3459,             234.45,         123.00,        '2012/12/03')

select b.claimid, c.lossreserve - isnull(d.lossreserve,0) as LossReserve,  c.losspaid - isnull(d.losspaid,0) as LossPaid
from

(select max([order]) as today, max([order])-1 as yesterday, claimid from
(
select rank() over (partition by claimid order by rundate asc) as [order],
claimid, lossreserve, losspaid
from @claims
) a
group by claimid) b

inner join

(select rank() over (partition by claimid order by rundate asc) as [order],
claimid, lossreserve, losspaid
from @claims) c

on b.claimid = c.claimid and b.today = c.[order] 

left outer join 

(select rank() over (partition by claimid order by rundate asc as [order],
claimid, lossreserve, losspaid
from @claims) d

on b.claimid = d.claimid and b.yesterday = d.[order]

Open in new window

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now