June 24, 2011 at 7:01 pm
Hi;
I thought i was doing this correctly; the anchor part of the query does what i expected, and the recursive part *almost* does, but it 'forgets' where it was and starts over on the third row...so theres something wrong with the expression im using to increment the running total, but i am stumped as to how to correct it. I wanted to add yet another column but my insticles told me down that road lay ruin.
The object of the game is to find both the day and amount on that day a claimant has attained a stop loss limit, and to calculate the total amount of the overage after they exceed it. It comes into play during case management and has to do with reinsurance recoupement, so its an important way to assess what we expect for reinsurance.
Here is the setup; I was using this as a temp table, but dont know how to dupe a table create statement out of tempdb, so i made it a hard table here.
CREATE TABLE [dbo].[StopLoss](
[memberid] [varchar](14) NULL,
[claimno] [char](16) NULL,
[primdate] [smalldatetime] NULL,
[net] [int] NULL,
[seq] [int] NULL,
[stoploss] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT STOPLOSS(memberid, claimno, primdate, net, seq, stoploss)
SELECT '100105502','0000000051109980','Aug 4 2008 12:00AM',2827,1,0 UNION ALL
SELECT '100105502','0000000052489020','Sep 16 2008 12:00AM',2707,2,0 UNION ALL
SELECT '100130601','0000000045088770','Jan 5 2008 12:00AM',603,1,0 UNION ALL
SELECT '100130601','0000000045862270','Jan 28 2008 12:00AM',2728,2,0 UNION ALL
SELECT '100143907','0000000045762720','Jan 9 2008 12:00AM',1123,1,0 UNION ALL
SELECT '100143907','0000000052488930','Sep 14 2008 12:00AM',2527,2,0 UNION ALL
SELECT '100202101','0000000045959670','Jan 30 2008 12:00AM',10270,1,0 UNION ALL
SELECT '100202101','0000000049121920','May 21 2008 12:00AM',15187,2,0 UNION ALL
SELECT '100202101','0000000050395270','May 27 2008 12:00AM',4693,3,0 UNION ALL
SELECT '100210103','0000000051658400','Jul 10 2008 12:00AM',68425,1,0 UNION ALL
SELECT '100210103','0000000052006940','Aug 25 2008 12:00AM',3750,2,0 UNION ALL
SELECT '100210103','0000000052110010','Aug 30 2008 12:00AM',5676,3,0 UNION ALL
SELECT '100210103','0000000052746970','Sep 18 2008 12:00AM',5676,4,0 UNION ALL
SELECT '100350106','0000000047345540','Mar 13 2008 12:00AM',1293,1,0 UNION ALL
SELECT '100350106','0000000047961340','Mar 13 2008 12:00AM',8036,2,0 UNION ALL
SELECT '100350106','0000000048622040','Apr 23 2008 12:00AM',3492,3,0 UNION ALL
SELECT '100350106','0000000048685300','Apr 29 2008 12:00AM',13205,4,0 UNION ALL
SELECT '100413906','0000000047562820','Mar 24 2008 12:00AM',699,1,0 UNION ALL
SELECT '100413906','0000000048181120','Mar 25 2008 12:00AM',7066,2,0 UNION ALL
SELECT '100413906','0000000052274790','Aug 25 2008 12:00AM',10854,3,0 UNION ALL
SELECT '100413906','0000000052716460','Sep 7 2008 12:00AM',10854,4,0 UNION ALL
SELECT '100437502','0000000046550740','Jan 11 2008 12:00AM',2753,1,0 UNION ALL
SELECT '100437502','0000000048181810','Mar 10 2008 12:00AM',11756,2,0 UNION ALL
SELECT '100437502','0000000048431140','Apr 8 2008 12:00AM',3253,3,0 UNION ALL
SELECT '100437502','0000000052080660','Aug 16 2008 12:00AM',4826,4,0 UNION ALL
SELECT '100437502','0000000053438080','Sep 22 2008 12:00AM',5316,5,0 UNION ALL
SELECT '101405601','0000000048356610','Apr 30 2008 12:00AM',4369,1,0 UNION ALL
SELECT '101405601','0000000050817050','Jul 25 2008 12:00AM',2721,2,0 UNION ALL
SELECT '101405601','0000000052688800','Sep 24 2008 12:00AM',1232,3,0 UNION ALL
SELECT '101405601','0000000053898550','Sep 25 2008 12:00AM',19438,4,0 UNION ALL
SELECT '101405601','0000000054214500','Oct 29 2008 12:00AM',3487,5,0 UNION ALL
SELECT '101405601','0000000055094060','Dec 10 2008 12:00AM',5133,6,0 UNION ALL
SELECT '108492701','0000000048216510','Apr 22 2008 12:00AM',12577,1,0 UNION ALL
SELECT '108492701','0000000049522960','May 8 2008 12:00AM',11606,2,0 UNION ALL
SELECT '108492701','0000000050108770','Jun 6 2008 12:00AM',9825,3,0 UNION ALL
SELECT '108492701','000000005010877A','Jun 6 2008 12:00AM',7276,4,0 UNION ALL
SELECT '108492701','0000000051339990','Jul 18 2008 12:00AM',20876,5,0 UNION ALL
SELECT '108553404','0000000045354750','Jan 10 2008 12:00AM',5494,1,0 UNION ALL
SELECT '108553404','0000000048623170','Apr 21 2008 12:00AM',5638,2,0 UNION ALL
SELECT '108553404','0000000049324210','May 17 2008 12:00AM',7102,3,0 UNION ALL
SELECT '108553404','0000000053124350','Sep 22 2008 12:00AM',4322,4,0 UNION ALL
SELECT '108553404','0000000054927670','Nov 19 2008 12:00AM',4322,5,0 UNION ALL
SELECT '108553404','0000000055296430','Dec 5 2008 12:00AM',5586,6,0
and the cte
;WITH myCte (memberid, claimno, primdate, net, seq, stoploss)
AS (
SELECT memberid, claimno, primdate, net, seq, stoploss
FROM StopLoss
WHERE seq = 1
UNION ALL
SELECT a.memberid, a.claimno, a.primdate, a.net, a.seq, a.net + Isnull(mycte.net, 0)
FROM StopLoss a
JOIN myCte ON a.memberid = mycte.memberid
AND a.seq = mycte.seq + 1
)
SELECT * FROM myCte
ORDER BY memberid, seq
I thought this bit was correct <<a.net + Isnull(mycte.net, 0) >>, but it isnt getting it.
I hope the setup is adequate...if it needs anything else please do not hesitate to ask.
Thanks very much for your help
drew
June 24, 2011 at 8:39 pm
see if this does what you want
;with myCte (memberid, claimno, primdate, net, seq, stoploss)
as (select memberid, claimno, primdate, net, seq, stoploss
from StopLoss
where seq = 1
union all
select a.memberid, a.claimno, a.primdate, a.net, a.seq,
case
when mycte.seq = 1 then a.net + Isnull(mycte.net, 0)
else a.net + Isnull(mycte.stoploss, 0)
end as stoploss
from StopLoss a
inner join myCte on a.memberid = mycte.memberid
and a.seq = mycte.seq + 1
)
select *
from myCte
order by memberid, seq
June 24, 2011 at 10:00 pm
thanks so much...
its doing what i need.
drew
June 25, 2011 at 1:34 pm
drew.georgopulos (6/24/2011)
thanks so much...its doing what i need.
drew
It looks like you could have a whole lot of rows in the source table. How's the performance with the recursive CTE on this?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2011 at 2:42 pm
like Carl Sagan used to say...
it needed a select distinct on the final query, but it worked.
I can't thank you and Old Hand enough for opening my eyes.
best
drew
June 25, 2011 at 5:34 pm
That still doesn't say much about the performance, Drew. How many rows are you processing with this and about how long does it take? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2011 at 8:10 pm
its barely enough to move the needle...heres the gory details...
i do a datediff in ms from start to finish
declare @start datetime
declare @end datetime
then set up #t1 from institutional header and institutional detail to get one row a claim. The money is on the detail lines, so i sum them. sometimes professional claims get into the institutional data so i check place of service to be 21. because each year has its own stop loss limit, i filter by that too.
IF OBJECT_ID('TempDB..#t1','U') IS NOT NULL DROP TABLE #t1
set @start = getdate()
SELECT Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) AS memberid,
a.claimno,
a.primdate,
sum(CAST(net AS INT)) / 100 AS net ,
row_number() over (partition by Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) order by a.primdate) seq,
cast(0 as bigint) stoploss
INTO #t1
FROM insth a--71116 rows
JOIN instd b--227948 rows
ON ( a.claimno = b.claimno
AND a.primdate = b.primdate )
WHERE place = 21
AND CAST(net AS INT) / 100 > 0
AND year(a.primdate)=@year
GROUP BY Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)),
a.claimno,
a.primdate
ORDER BY memberid, a.primdate --Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)), a.primdate
and then i use the CTE to make the running totals
;with myCte (memberid, claimno, primdate, net, seq, stoploss)
as (select memberid, claimno, primdate, net, seq, stoploss
from #t1
where seq = 1
union all
select a.memberid, a.claimno, a.primdate, a.net, a.seq,
case
when mycte.seq = 1 then a.net + Isnull(mycte.net, 0)
else a.net + Isnull(mycte.stoploss, 0)
end as stoploss
from #t1 a
inner join myCte on a.memberid = mycte.memberid
and a.seq = mycte.seq + 1
)
select *
from myCte
group by memberid, claimno, primdate, net, seq, stoploss
having stoploss >=@limit
order by memberid, primdate
set @end = getdate()
select datediff(ms,@start,@end)
the message page looks like this
(14677 row(s) affected) this is the contents of #t1 after the sum
(73 row(s) affected) this is what is returned from the cte
(1 row(s) affected) this is the datediff, which is between 489 and 513ms
the only thing missing is that when theres more than one claim in the sequence...i lose the claim number from the first claim when there is more than one...if the first claim is bigger thant the stop loss limit, then of course, its there.
thanks again
drew
June 25, 2011 at 10:22 pm
That's a fairly small number of rows. Be advised that you have a time-bomb ticking in your first query.
AND CAST(net AS INT) / 100 > 0
I'm pretty sure that will cause a table scan or, at best, an index scan (sames as a table scan if it's the clustered index). It's not likely an annoyance to you now, but if the tables scale up in the future, your code could become quite slow. I believe you could get around this by using the following...
AND net > 100
... to keep that bit of criteria SARGable for the future.
I was really concerned about the recurrsive CTE. They tend to be slower than a While Loop and they use a whole lot more resources. On a million rows, this one isn't too bad... it only takes a little over 2 minutes to resolve and only creates 16.8 million reads. 😛 I realize that you're no where near that size now, but if your scale grows to where the CTE becomes a problem, c'mon back and I'll show you how to do a million rows in something under 7 seconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2011 at 9:07 am
Thanks a ton, will do.
My issue with the cast is that, believe it or not, net is delivered as a varchar, and so i cannot sum it, so that's why its wrapped in a cast...
I was afraid i was wearing out my welcome, but cannot resist learning more about it, and really appreciate your input. Your last insight about the implicit conversion of int to datetime was a mindblower, and relieved me of having to do the same kind of conversion there, but implicit conversion doesnt do the same thing between varchar and int (do the lifting for me with net...)
best
drew
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply