March 9, 2011 at 10:12 am
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 rows
Line table - 360k rows
Payments table - 1,162k rows
I 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 advance
DECLARE @incurredStartDate datetime = '01/01/1991'
DECLARE @incurredEndDate datetime = '1/1/2011'
DECLARE @yearsOut int = 20
1.
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 P
INNER JOIN Claim c on c.ClaimID = P.ClaimID
LEFT JOIN ClaimLineItem l on c.ClaimID = l.ClaimID and
P.ClaimLineItemID = l.ClaimLineItemID
LEFT JOIN ClaimLineCoverageType cl on cl.ClaimLineCoverageTypeID = l.ClaimLineCoverageTypeID
order by c.StateCode, c.SectionCode, c.SeqNum, cl.LineNum
An 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
March 9, 2011 at 10:31 am
Just a suggestion, seemingly might help with performance as this would get rid of that CTE. Anyhow, here's how I would do it...
Create a table (perm, temp, or variable -- your choice) to hold your yearly totals (that is, a total for only that year). Something like (ClaimYear, TotalClaimAmount)
Populate that table with a much simplier select statement, grouping by year and suming claims.
Do your pivot off of this table. Or maybe write a function (UDF) that sums all the years less than or equal any given year. Given this is a very small table (only 20 rows), this should be quite preformant.
Good luck.
March 9, 2011 at 10:46 am
This is a total for each claim by year though starting at its start date. It is not a total of just years.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply