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?

score 1 · Accepted Answer · answered Sep 11 '17 at 10:35

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.

score 1 · Answer 2 · answered Sep 11 '17 at 10:36

1

j,

SQL Server 2012 has a number of new functions for example SQL Lead() and SQL Lag() functions. You can use the SQL Server Lag() function for the solution of your requirement

Here is my solution

with cte as (
select 
    date, flag, 
    LAG(flag, 1, NULL) OVER (ORDER BY date) AS preVal,
    case when flag = ISNULL( ( LAG(flag, 1, NULL) OVER (ORDER BY date) ), flag) then 0 else 1 end as i
from recordset
)
select
    cte.date,
    cte.flag,
    sum(cte2.i)+1 r
from cte
inner join cte as cte2 on cte.date >= cte2.date
group by cte.date, cte.flag
order by cte.date

answered Sep 11 '17 at 10:36

Eralper

6,461
2
21
27

your solution is good and working, thank you. I preferred gotqn's one because it is more linear thus readable, and he uses the rows/range option clause of the OVER BY command. – RaffaeleT Sep 11 '17 at 12:40
1

no problem :) I also learnt the "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" option which is better to continue with – Eralper Sep 11 '17 at 13:28

score 0 · Answer 3 · answered Sep 11 '17 at 11:43

You could populate it in a cursor like this:

DECLARE  @Table TABLE (
[Date] [nvarchar](50) NULL,
[flag] [int] NULL,
[Number] [int] NULL
) 

DECLARE @Date nvarchar(50)
DECLARE @flag int
DECLARE @number int
DECLARE @flagnumber int
DECLARE @flagincrement int

DECLARE  MyCursor CURSOR FOR
SELECT [Date],FLag
FROM [LegOgSpass].[dbo].[testflag]
Order by [Date]

OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @Date,@flag

IF (@@FETCH_STATUS>=0)
BEGIN

SET @number = 1
SET @flagincrement = @flag

INSERT INTO @Table ([Date],[Flag],[Number])
VALUES(@Date,@flag,@number)

FETCH NEXT FROM MyCursor INTO @Date,@flag
END
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@@FETCH_STATUS<>-2)
IF @flagincrement = @flag 
SET @number = @number 

ELSE
SET @number = @number+1
SET @flagincrement = @flag

INSERT INTO @Table ([Date],[Flag],[Number])
VALUES(@Date,@flag,@number)
FETCH NEXT FROM MyCursor INTO @Date,@flag
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT * FROM @Table

Your solution is acceptable, thank you, but I am trying to avoid cursors and iterations: I am updating the question. — RaffaeleT, Sep 11 '17 at 12:21
I know, i just wanted to give an example- Glad you got it to work :) — SqlKindaGuy, Sep 11 '17 at 13:02

T-SQL progressive numbering partitions

3 Answers3