and I want output as :
from first to fourth row Group ID must be 1
fifth row Group ID 2
sixth row group ID 3
seventh row groupid 4
eight row group id 5
ninth row group id 6
tenth row group id 7
eleventh and twelve row group id 8
================================================
I have tried this
;WITH cte_StepOne as ( SELECT ROW_NUMBER() OVER (partition by a.id ORDER BY a.[id], a.[START_TIME], a.[END_TIME]) AS SeqNo, a.[id], a.[START_TIME], a.[END_TIME], b.[id] AS OverLapID, b.[START_TIME] AS [OverLapEffectiveDate], b.[END_TIME] AS [OverLapEnddate] FROM ACTIVITY1 a LEFT JOIN ACTIVITY1 b ON a.START_TIME BETWEEN b.START_TIME AND b.END_TIME AND a.START_TIME <> b.START_TIME AND a.END_TIME <> b.END_TIME --and a.ID <> b.ID ) ,cte_StepTwo AS ( SELECT SeqNo, id, START_TIME, END_TIME, LEAD(OverLapEffectiveDate, 1) OVER (ORDER BY SeqNo,id) AS LeadValue,LAG(id, 1) OVER (ORDER BY SeqNo,id) AS LeadValueID, OverLapID, OverLapEffectiveDate, OverLapEnddate FROM cte_StepOne ) ,cte_Result AS ( SELECT id, START_TIME, END_TIME, CASE WHEN LeadValue = START_TIME AND OverLapEffectiveDate IS NULL THEN ID WHEN OverLapID IS NULL THEN LeadValueID + 1 ELSE OverLapID END AS OverLapID, CASE WHEN LeadValue = START_TIME AND OverLapEffectiveDate IS NULL THEN START_TIME ELSE OverLapEffectiveDate END AS OverLapEffectiveDate, CASE WHEN LeadValue = START_TIME AND OverLapEffectiveDate IS NULL THEN END_TIME ELSE OverLapEnddate END AS OverLapEnddate FROM cte_StepTwo ) SELECT DISTINCT id, START_TIME, END_TIME, DENSE_RANK() OVER (ORDER BY ID,OverLapID) AS GroupID FROM cte_Result ORDER BY id,START_TIME
but not producing the required result
please help
