How to mark consequitive groups with non-unique group key

  • 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/61537
  • Thank you, this seems to work for me, appreciate quick reply

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

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