Running Total by recursive CTE only works right on the first two rows??

  • 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

  • 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

  • thanks so much...

    its doing what i need.

    drew

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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