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 (
ID int
, TS date
, FLAG bit
);
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
ID TS FLAG GRP
1 2020-01-01 0 1
1 2020-01-02 0 1
1 2020-01-03 0 1
1 2020-01-04 1 2
1 2020-01-05 1 2
1 2020-01-06 1 2
1 2020-01-07 0 3
2 2020-05-01 0 1
2 2020-05-05 1 2
2 2020-05-15 0 3
2 2020-05-20 1 4
2 2020-05-25 1 4
2 2020-05-30 0 5
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:
ID TS FLAG GRP
1 2020-01-01 0 0
1 2020-01-02 0 0
1 2020-01-03 0 0
1 2020-01-04 1 3
1 2020-01-05 1 3
1 2020-01-06 1 3
1 2020-01-07 0 3
2 2020-05-01 0 3
2 2020-05-05 1 5
2 2020-05-15 0 4
2 2020-05-20 1 6
2 2020-05-25 1 6
2 2020-05-30 0 6
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
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/61537June 10, 2021 at 4:26 pm
Thank you, this seems to work for me, appreciate quick reply
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