|
midavis
Starting Member
23 Posts |
 Posted - 2011-03-09 : 09:42:17
|
| I am needing to totals up payments for claims that started up to 20 years ago. I will be showing a running total by year. Year 1 is the first year, Year2 is the second, etc. What I will have is 20 years out worth of running totals even if I am only in the second year of a claim. I have a solution that works and I will describe it below but it seems to run really slow. I am using a lot of common table expressions and a cross join to fill in the empty years These are the steps I am taking.1. Using a common table expressions I am selecting all payments by year for each claim. This gives me a starting point that has every claim in the system plus a year by year breakdown of the payments.2. I am building on this common table expression with another. I join the data on itself to get a running total by year and then partition it by the Claim again so I can setup the next qurey. This sets up my cross join so I can fill in the remaining bits of data I need.3. I union the current data with a cross join of the latest payment year for each claim up to 20 years out (this is why i partitioned the data earlier)4. From here I have the data I need and I just do a pivot to get the rows of data into columns.The query seems to work fine but when I do a cross join at step 3 it takes quite a bit of time. The table statistics are below.Claim table - 330k rowsLine table - 360k rowsPayments table - 1,162k rowsI have seen people with 10s of millions of rows of data with quick queries. Mine taks around 1 minute to run. It takes around 5 seconds up to the point of the cross join. Any other solutions would be of great help. Samples of the steps are below. Thanks in advanceDECLARE @incurredStartDate datetime = '01/01/1991'DECLARE @incurredEndDate datetime = '1/1/2011'DECLARE @yearsOut int = 201.WITH cte AS( SELECT c.ClaimID, c.IncidentDt, ISNULL(p.ClaimLineItemID, 0) as ClaimLineItemID, ISNULL(SUM(p.CheckAmount), 0) as amount, ISNULL(Year(p.CheckDate), 0) as paymentYear FROM Claim c LEFT JOIN ClaimLineItem l on l.ClaimID = c.ClaimID LEFT JOIN ClaimPayment p on p.ClaimLineItemID = l.ClaimLineItemID where c.IncidentDt >= @incurredStartDate and c.IncidentDt < @incurredEndDate GROUP BY c.ClaimID, c.IncidentDt, p.ClaimLineItemID, YEAR(p.CheckDate))2.,cte2 AS( Select ROW_NUMBER() OVER (PARTITION BY O1.ClaimID, O1.ClaimLineItemID ORDER BY O1.ClaimID, O1.ClaimLineItemID, O1.paymentYear DESC) as rn, O1.ClaimID, O1.IncidentDt, O1.ClaimLineItemID, O1.paymentYear, SUM(O2.amount) as runningTotal From cte O1 INNER JOIN cte O2 on O2.ClaimID = O1.ClaimID and O2.ClaimLineItemID = O1.ClaimLineItemID AND O2.paymentYear <= O1.paymentYear Group By O1.ClaimID, O1.IncidentDt, O1.ClaimLineItemID, O1.paymentYear)3.,cte3 as( select ClaimID, IncidentDt , ClaimLineItemID, paymentYear, runningTotal from cte2 -- union with all possible years after the latest payment UNION Select ClaimID, IncidentDt , ClaimLineItemID, A.newYear, runningTotal from (select ClaimID, IncidentDt, ClaimLineItemID, paymentYear, runningTotal from cte2 c where rn = 1) D CROSS APPLY (Select Year(DateAdd(yy, n.n - 1, D.IncidentDt)) newYear From Nums n Where n.n <= @yearsOut and Year(DateAdd(yy, n.n - 1, D.IncidentDt)) > D.paymentYear ) AS A)4.,cte4 as( select cte3.ClaimID, cte3.ClaimLineItemID, cte3.paymentYear, cte3.runningTotal, c.IncidentDt, 'amount' + CAST(paymentYear - YEAR(c.incidentDt) + 1 as varchar(3)) as PaymentYearDisplay from cte3 join Claim c on c.ClaimID = cte3.ClaimID LEFT Join ClaimLineItem l on l.ClaimID = c.ClaimID and l.ClaimLineItemID = cte3.ClaimLineItemID)select c.StateCode, c.SectionCode, c.SeqNum, cl.LineNum, ISNULL(P.[amount1], 0) as [amount1], ISNULL(P.[amount2], 0) as [amount2], ISNULL(P.[amount3], 0) as [amount3], ISNULL(P.[amount4], 0) as [amount4], ISNULL(P.[amount5], 0) as [amount5], ISNULL(P.[amount6], 0) as [amount6], ISNULL(P.[amount7], 0) as [amount7], ISNULL(P.[amount8], 0) as [amount8], ISNULL(P.[amount9], 0) as [amount9], ISNULL(P.[amount10], 0) as [amount10], ISNULL(P.[amount11], 0) as [amount11], ISNULL(P.[amount12], 0) as [amount12], ISNULL(P.[amount13], 0) as [amount13], ISNULL(P.[amount14], 0) as [amount14], ISNULL(P.[amount15], 0) as [amount15], ISNULL(P.[amount16], 0) as [amount16], ISNULL(P.[amount17], 0) as [amount17], ISNULL(P.[amount18], 0) as [amount18], ISNULL(P.[amount19], 0) as [amount19], ISNULL(P.[amount20], 0) as [amount20]FROM (Select ClaimID, ClaimLineItemID, IncidentDt, runningTotal, PaymentYearDisplay FROM cte4 ) as OV PIVOT(sum(runningTotal) FOR PaymentYearDisplay IN ([amount1], [amount2], [amount3], [amount4], [amount5], [amount6], [amount7], [amount8], [amount9], [amount10], [amount11], [amount12], [amount13], [amount14], [amount15], [amount16], [amount17], [amount18], [amount19], [amount20] )) AS PINNER JOIN Claim c on c.ClaimID = P.ClaimIDLEFT JOIN ClaimLineItem l on c.ClaimID = l.ClaimID and P.ClaimLineItemID = l.ClaimLineItemIDLEFT JOIN ClaimLineCoverageType cl on cl.ClaimLineCoverageTypeID = l.ClaimLineCoverageTypeIDorder by c.StateCode, c.SectionCode, c.SeqNum, cl.LineNumAn example of the data is below. In this case the first payment for this claim did not start until 2009 when the claim was in its second year. A payment was made in Year 2, Year 3 and Year 4. After that you just basically see the total of the claim up to year 20.ClaimID IncidentDate Year1 Year2 Year3 Year4 Year5 ...... Year20 1 12/1/2008 0 1 3 5 5 5 |
|
Running Totals for the past 20 years