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