break row_number() sequence based on flag variable

Question

Hoping to find something.

I have data as shown below

id  month flag
111 jan   1
111 feb   1
111 mar   1
111 apr   0
111 may   0
111 jun   1
222 jan   1
222 feb   1
222 mar   0
222 apr   0
222 may   0
222 jun   1

I looking for the output as below

id  month flag order
111 jan   1    1
111 feb   1    2
111 mar   1    3
111 apr   0    1
111 may   0    2
111 jun   1    1
222 jan   1    1
222 feb   1    2
222 mar   0    1
222 apr   0    2
222 may   0    3
222 jun   1    1

I tried row_number() but the problem is we cannot break the sequence and start over. At an overall level, when ever there is a change in flag variable from 0 to 1 or 1 to 0 I need to start counting from 1 for each id separately

Chris Mack · Accepted Answer · 2017-10-19 14:33:44Z

2

Assuming SQL Server, here is an example:

DECLARE @T table (id int, [month] char(3), flag bit)

INSERT INTO @T
VALUES
(111, 'jan', 1)
,(111, 'feb', 1)
,(111, 'mar', 1)
,(111, 'apr', 0)
,(111, 'may', 0)
,(111, 'jun', 1)
,(222, 'jan', 1)
,(222, 'feb', 1)
,(222, 'mar', 0)
,(222, 'apr', 0)
,(222, 'may', 0)
,(222, 'jun', 1)

SELECT
    id
    , [month]
    , flag
    , ROW_NUMBER() OVER (PARTITION BY id, section ORDER BY monthNum) [order]
FROM
    (
        SELECT
            id
            , [month]
            , monthNum
            , flag
            , SUM(CASE WHEN newValue = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY monthNum) section
        FROM
            (
                SELECT
                    id
                    , [month]
                    , monthNum
                    , flag
                    , CASE WHEN LAG(flag, 1, ABS(flag - 1)) OVER (PARTITION BY id ORDER BY monthNum) = flag THEN 0 ELSE 1 END newValue
                FROM
                    (
                        SELECT
                            id
                            , [month]
                            , MONTH(CAST('1 ' + [month] + ' 17' AS datetime)) monthNum
                            , flag
                        FROM @T
                    ) Q
            ) Q2
    ) Q3

answered Oct 19, 2017 at 14:33

Chris Mack

5,2082 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

santhosh kulkarni Over a year ago

My mistake I need to do it in postgresql

santhosh kulkarni Over a year ago

I am able to do it in postgres. Thank you

Collectives™ on Stack Overflow

break row_number() sequence based on flag variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related