question

furqan avatar image
furqan asked

help in query required

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

sql query
input.jpg (25.7 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.