September 17, 2012 at 4:34 pm
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.
September 17, 2012 at 7:21 pm
For a working solution, you'll need to give us a little more:
1. DDL to CREATE a table
2. Consumable sample data (INSERT/SELECT/UNION ALL SELECT), and
3. A little more information on how the calculation is to be performed
I believe this article has your solution: http://www.sqlservercentral.com/articles/T-SQL/68467/.
Presumably you're looking to calculate lossreserve and losspaid as cumulative amounts:
lossreserve = previous lossreserve - current losspaid
losspaid = previous losspaid + current losspaid
The referenced article will provide you the tools you need to do this.
A recursive (counting) CTE is another option but it won't be as fast. This article has similar rCTEs that calculate depreciation and amortization schedules: http://www.sqlservercentral.com/articles/T-SQL/90955/
You'll need to base the anchor leg of the rCTE on the minimum RunDate and then work forward from there one day at a time.
Here's an example of the latter that is probably not 100% right but should get you close to what you're looking for:
DECLARE @Claims TABLE
(claimid INT, lossreserve MONEY, losspaid MONEY, RunDate DATETIME)
INSERT INTO @Claims
SELECT 3456,100.00,450.00,'2012-12-01'
UNION ALL SELECT 3457,200.00,150.00,'2012-12-01'
UNION ALL SELECT 3458,239.00,2000.00,'2012-12-01'
UNION ALL SELECT 3459,20.00,50.00,'2012-12-01'
UNION ALL SELECT 3456,300.00,156.00,'2012-12-02'
UNION ALL SELECT 3457,654.00,456.00,'2012-12-02'
UNION ALL SELECT 3458,900.00,213.00,'2012-12-02'
UNION ALL SELECT 3459,12.00,34.00,'2012-12-02'
UNION ALL SELECT 3461,145.00,29.00,'2012-12-02'
UNION ALL SELECT 3456,245.00,98.00,'2012-12-03'
UNION ALL SELECT 3457,345.00,987.00,'2012-12-03'
UNION ALL SELECT 3458,345.00,567.00,'2012-12-03'
UNION ALL SELECT 3459,234.45,123.00,'2012-12-03'
;WITH Anchor AS (
SELECT ClaimID, lossreserve, losspaid, RunDate
,rn=ROW_NUMBER() OVER (PARTITION BY ClaimID ORDER BY RunDate)
FROM @Claims
),
LossCalc AS (
SELECT claimid, lossreserve, losspaid, rundate
,CumLossReserve=lossreserve - losspaid
,CumLossPaid=losspaid
FROM Anchor
WHERE rn=1
UNION ALL
SELECT a.claimid, a.lossreserve, a.losspaid, a.rundate
,CumLossReserve=b.lossreserve - a.losspaid
,CumLossPaid=b.losspaid + a.losspaid
FROM @Claims a
INNER JOIN LossCalc b
ON a.claimid = b.claimid AND a.rundate = b.rundate + 1
)
SELECT *
FROM LossCalc
ORDER BY claimid, rundate
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply