using recursive CTE with SQL to group data based on a triggering condition

Question

I am working with a dynamic dataset that needs to be broken up and inserted based on a series of triggers. Dataset Example

In the example, the trigger is the changeover column on the right. What i would like to do is add an additional column that i can group this information by the timestamp value between the first entry that came in, the changeovers, and the last entry.

So far i've organized the project into CTE's to build those triggers and get this far. but i am running out of ideas on how to utlize CTE's to push this any further. Local resources are telling me i need a while loop, these resources are also not used to utilizing CTE's for their data so what i am doing is sort of foreign to them.

looks like a recursive CTE might help here, I am new to these and have been playing around with it for the past few days.

here is the direction i've been heading with the code:

Recursive_CTE(StemID,MasterStem,DateTimeStamp,changeover,InsertID)
as
(
SELECT

     StemID
    ,MasterStem
    ,changeover
    ,datetimestamp
    ,0 as InsertID

FROM CHANGEOVER_CTE
WHERE Masterstem is null
Union ALL
SELECT

    ccte.StemID
    ,ccte.MasterStem
    ,ccte.DateTimeStamp
    ,ccte.changeover
    ,case when (ccte.datetimestamp = rc.changeover)
        then
        rc.insertID + 1
        Else
        rc.InsertID
        end

FROM   CHANGEOVER_CTE as ccte
inner join Recursive_CTE as rc on ccte.datetimestamp = rc.changeover

and the results

problem is, once I hit the first NULL value, the passes of the CTE stop, what i'd like to do is repeat the sequence until the next trigger is hit, then increment that sequence and repeat.

Is CTE recursion the right direction here? i'm probably writing it incorrectly (i've re-written it several dozen times at this point)

any suggestions on how i can solve this dilemma?

starts from a null and ends on a null.. why don't you grab the min(date) and put it in the first row and grab the max date and put in the last row on the fly and that should solve your problem or even make it a rule that when one starts your change over starts and same when their shift ends — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jun 14, 2017 at 21:13
Thanks! i hadn't thought to use Min/Max. This actually solves the problem of having a reliable anchor. my recursion is still being killed off in the second row however, i think this is because i am trying to look at datetimestamp and then changeover and when i hit that first null value they obviously dont match and my join dies there. i'm struggling with re-writing it to look at the entire set of data and only change the InsertID when it finds a value in the changeover. — Markseph
– Markseph, Commented Jun 14, 2017 at 21:51
A recursive CTE is every bit as bad as a loop. Also, the word "trigger" has a specific meaning in SQL that is quite different from what you appear to mean. — RBarryYoung
– RBarryYoung, Commented Jul 19, 2017 at 17:05
See my answer here (stackoverflow.com/a/11661714/109122) for an example of how to do this kind of thing with Windowed Aggregate functions in a CTE (subquery in this case, but easy to change to a CTE). — RBarryYoung
– RBarryYoung, Commented Jul 19, 2017 at 17:12

Markseph · Accepted Answer · 2017-07-19 16:59:48Z

1

to follow up on this issue. a recursive CTE was not the best approach. I found that by using a window aggregate function (sum over the partition created by my changeover flags), this allowed me to build an appropriate ordered list where I could perform aggregation. If you are looking for something similar, research window aggregate functions.

answered Jul 19, 2017 at 16:59

Markseph

312 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using recursive CTE with SQL to group data based on a triggering condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related