How to group data and provide index

Question

Hi Folks!!

I have data in a table as below

Id. Startdate. ColA. ColB.

1. 1 Jan 2020. A. B.
2. 2 Jan 2020. A. B.
3. 3 Jan 2020. A. C.
4. 4 Jan 202. A. C.
5. 5 Jan 2020. A. B.
6. 6 Jan 2020. A. B.
7. 7 Jan 2020. A. C.

Now I want to introduce one more column such as Index and that will provide same index to all the grouped record until date is in sequence and data will be like as below

Id. Startdate. ColA. ColB. index

1. 1 Jan 2020. A. B. 1
2. 2 Jan 2020. A. B. 1
3. 3 Jan 2020. A. C. 2
4. 4 Jan 202. A. C. 2
5. 5 Jan 2020. A. B. 3
6. 6 Jan 2020. A. B. 3
7. 7 Jan 2020. A. C. 4

Please suggest.

Thanks
Amit Srivastava

Answer 1

;With cteRn As
(Select Id, Startdate, ColA, ColB,
  Row_Number() Over(Order By Startdate, Id) As rn
From @Sample),
cteIndex As
(Select r.Id, r.Startdate, r.ColA, r.ColB, r.rn, 1 As [Index]
From cteRn r
Where r.rn = 1
Union All
Select r.Id, r.Startdate, r.ColA, r.ColB, r.rn, 
  Case When i.ColA = r.ColA And i.ColB = r.ColB Then i.[Index] Else i.[Index] + 1 End As [Index] 
From cteIndex i
Inner Join cteRn r On i.rn+1 = r.rn)
Select Id, Startdate, ColA, ColB, [Index] 
From cteIndex;

Tom

Answer 2

Check a suggestion:

;
with Q as
(
    select *,
        iif(lag(ColA) over (order by Startdate) <> ColA or lag(ColB) over (order by Startdate) <> ColB, 9, NULL) f
    from ATable
)
select Id, Startdate, ColA, ColB, count(f) over (order by Startdate) + 1 as [index]
from Q
order by Startdate

Answer 3

Hi @Viorel

I have done the above with some work around, as detailed below

Table : dbo.distinct_id_number: Will have only distinct Employee Employee Number with Row ID against every Employee NUmber.

Table : ATable : contain complete set of data.

Table : dbo.TablePostIndexInclusion : Will hold the new data with Proper Index.

DECLARE @csgo INT
DECLARE @Max INT
SET @Max = (select MAX(Row_Num) c from dbo.distinct_id_number)
--select @Max
SET @csgo =(select MIN(Row_Num) c from dbo.distinct_id_number)
WHILE ( @csgo <= @Max )
BEGIN
; with Q as
(
select *,
iif(lag(ColA) over (order by Startdate) <> ColA or lag(ColB) over (order by Startdate) <> ColB, 9, NULL) f
from ATable b (nolock) JOIN dbo.distinct_id_number a (nolock) on b.employee_number=a.employee_number
Where a.Row_num=@csgo
)
insert into dbo.TablePostIndexInclusion
select Id, Startdate, ColA, ColB, count(f) over (order by Startdate) + 1 as [index]
from Q
SET @csgo = @csgo + 1
END

Thanks
Amit