How to mark consequitive groups with non-unique group key

Question

Post reply

How to mark consequitive groups with non-unique group key

migurus

SSC-Addicted

Points: 456

June 10, 2021 at 7:54 am

Go to Answer

#3894274

I have a sequence of records with ID and FLAG being a key to identify the group of records, but the FLAG here is a bit, so it is not unique

declare @T table (
IDint
,TSdate
,FLAGbit
);
insert into @T(ID, TS, FLAG)
values
(1,'2020-01-01', 0 )
,(1,'2020-01-02', 0 )
,(1,'2020-01-03', 0)
,(1,'2020-01-04', 1)
,(1,'2020-01-05', 1)
,(1,'2020-01-06', 1)
,(1,'2020-01-07', 0)

,(2,'2020-05-01', 0)
,(2,'2020-05-05', 1)
,(2,'2020-05-15', 0)
,(2,'2020-05-20', 1)
,(2,'2020-05-25', 1)
,(2,'2020-05-30', 0)

I need to get this grouping

IDTSFLAGGRP
12020-01-0101
12020-01-0201
12020-01-0301
12020-01-0412
12020-01-0512
12020-01-0612
12020-01-0703
22020-05-0101
22020-05-0512
22020-05-1503
22020-05-2014
22020-05-2514
22020-05-3005

I tried this, but it does not work as it repeats GRP for records with different ID+FLAG key

select ID, TS, FLAG
,row_number() over(order by ID) - row_number() over(partition by FLAG order by TS)[GRP]
from @T
order by ID, TS

Results are:
IDTSFLAGGRP
12020-01-0100
12020-01-0200
12020-01-0300
12020-01-0413
12020-01-0513
12020-01-0613
12020-01-0703
22020-05-0103
22020-05-0515
22020-05-1504
22020-05-2016
22020-05-2516
22020-05-3006

I am OK with group numbers being not sequential, but my problem is for example 1 2020-01-07 0 3 - group 3 includes flag 1 and 0, that is a mistake. Similar problem is for group 6.

Any hints would be appreciated

Mark Cowne

One Orange Chip

Points: 26944
More actions
June 10, 2021 at 8:48 am
Answer
#3894305
```
with cte as (
select ID, TS, FLAG,
       case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else 1 end as isstart
from @T
)
select ID, TS, FLAG,
       sum(isstart) over(order by ID,TS) as GRP
from cte
order by ID, TS;
```
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
July 7, 2021 at 8:33 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

migurus SSC-Addicted Points: 456 More actions · Answer 1

Thank you, this seems to work for me, appreciate quick reply