Generate Identifier for consecutive rows with same value-CodePudding

I'm trying to get an SQL Server query that needs partitioning in a way such that consecutive rows with the same Type value ordered by date have the same unique identifier.

Let's say I have the following table

declare @test table 
              (
                  CustomerId varchar(10),
                  Type int,
                  date datetime
              )

insert into @test values ('aaaa', 1, '2015-10-24 22:52:47')
insert into @test values ('bbbb', 1, '2015-10-23 22:56:47')
insert into @test values ('cccc', 2, '2015-10-22 21:52:47')
insert into @test values ('dddd', 2, '2015-10-20 22:12:47')
insert into @test values ('aaaa', 1, '2015-10-19 20:52:47')
insert into @test values ('dddd', 2, '2015-10-18 12:52:47')
insert into @test values ('aaaa', 3, '2015-10-18 12:52:47')

I want my output column to be something like this (the numbers do not need to be ordered, all I need are unique identifiers for each group).

Explanation: first 2 rows have UD:0 because the both have a type "1", then the next row has a different type ("2") so it should be another identifier, UD:1 in this case, the following row still has the same type so the UD is the same, then the next one has a different type "1" so another identifier, in this case UD:2 and on and on.

The customerId column is irrelevant to the query, the condition should be based on the Type and Date column

My current almost does the trick but it fails in some cases giving the same ID to rows with different type values.

SELECT 
    ROW_NUMBER() OVER (ORDER BY date) - 
        ROW_NUMBER() OVER (PARTITION BY Type ORDER BY date)
FROM @TEST

CodePudding user response：

This is a Gaps & Islands problem that is solved using the traditional solution.

For example:

select
  *,
  sum(inc) over(order by date desc, type) as grp
from (
  select *,
    case when type <> lag(type) over(order by date desc, type)
      then 1 else 0 end as inc
  from test
) x
order by date desc, type

Result:

 CustomerId  Type  date                  inc  grp 
 ----------- ----- --------------------- ---- --- 
 aaaa        1     2015-10-24T22:52:47Z  0    0   
 bbbb        1     2015-10-23T22:56:47Z  0    0   
 cccc        2     2015-10-22T21:52:47Z  1    1   
 dddd        2     2015-10-20T22:12:47Z  0    1   
 aaaa        1     2015-10-19T20:52:47Z  1    2   
 dddd        2     2015-10-18T12:52:47Z  1    3   
 aaaa        3     2015-10-18T12:52:47Z  1    4

See example at SQL Fiddle.