T-SQL progressive numbering partitions

Question

I am aiming to obtain a record set like this

date   flag   number
01     0      1
02     0      1
03     1      2
04     1      2
05     1      2
06     0      3
07     1      4
08     1      4

I start from the record set with "date" and "flag" only. I am trying to compute the "number" column by using T-SQL ranking and partitioning functions.

A normal ranking would give a result like this:

 date   flag   number
    01     0      1
    02     0      1
    03     1      2
    04     1      2
    05     1      2
    06     0      1
    07     1      2
    08     1      2

Any suggestion?

Answer 1

You can try this:

DECLARE @DataSource TABLE
(
    [date] CHAR(2)
   ,[flag] BIT
);

INSERT INTO @DataSource ([date], [flag])
VALUES ('01', 0)
      ,('02', 0)
      ,('03', 1)
      ,('04', 1)
      ,('05', 1)
      ,('06', 0)
      ,('07', 1)
      ,('08', 1);

WITH DataSource ([date], [flag], [number]) AS
(
    SELECT [date]
          ,[flag]
          ,IIF(LAG([flag], 1, NULL) OVER (ORDER BY [date]) = [flag], 0, 1) 
    FROM @DataSource
)
SELECT [date]
      ,[flag]
      ,SUM([number]) OVER (ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [number]
FROM DataSource
ORDER BY [date], [flag];

The idea is to check when group is changed using the LAG function:

IIF(LAG([flag], 1, NULL) OVER (ORDER BY [date]) = [flag], 0, 1) 

Then, using the BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to SUM group changes.