SQL Server Discover change in status over time with reoccurring status

plupiseo  于 2023-03-17  发布在  其他
关注(0)|答案(1)|浏览(118)

I am trying to calculate time difference between specific statuses. I need to create a fictive group (Result_Group) based on category. The Group id should change every time category changes. If a category occurs again, but non-subsequent, it should count as a new fictive group.

I have tried to use partitions and recursive CTE to solve this, but without any luck. enter image description here

Tried to use partitioning and recursive CTE's

vlju58qv

vlju58qv1#

You can do something like this:

with cte as (
    select *
    from (
        values ('A', getdate() - 10)
        ,   ('A', getdate() - 9.7)
        ,   ('A', getdate() - 9.2)
        ,   ('A', getdate() - 9.1)
        ,   ('B', getdate() - 9.05)
        ,   ('B', getdate() - 9.0)
        ,   ('B', getdate() - 8)
        ,   ('C', getdate() - 5)
        ,   ('B', getdate() - 3)
        ,   ('A', getdate() - 2)
        ,   ('B', getdate() - 1)
        
    ) c (category, dt)
    )
select SUM(case when ISNULL(prevcat, category) = category then 0 else 1 end) over(order by dt) AS grp
,   *
from (
    select  *
    ,   lag(category) over(order by dt) as prevcat
    from cte
    ) x

I created some dummy data since you didn't provide it in a easy to use table.

The technique is pretty standard, you get the previous value ordered by something, then you create a running sum (or count) that accumulates a counter. We want to keep the counter the same if previous and current categories match and this enables to create a nice running group.

One caveat is if more than one date match, you should have some kind of tiebreaker for the ORDER BY part.

相关问题