CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012partitioningranking
RaffaeleT
RaffaeleT

Reputation: 264

T-SQL progressive numbering partitions

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?

Upvotes: 1

Views: 136

Answers (3)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

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

Upvotes: 0

Eralper
Eralper

Reputation: 6622

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

Upvotes: 1

gotqn
gotqn

Reputation: 43646

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];

enter image description here

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.

Upvotes: 1

Related Questions