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
1条答案
按热度按时间vlju58qv1#
You can do something like this:
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.