Solution to TSQL Challenge 27 - Identify overlapping weekly schedules of Nurses By patelkhyati82

;WITH Dates AS
(SELECT SchID,Patient,Nurse,dy,St,En,
		CASE Dy WHEN 'Mon' THEN 1
		WHEN 'Tue' THEN 2
		WHEN 'Wed' THEN 3
		WHEN 'Thu' THEN 4
		WHEN 'Fri' THEN 5
		WHEN 'Sat' THEN 6
		WHEN 'Sun' THEN 7
	END AS DyNo,
	CASE Dy WHEN 'Mon' THEN '2010-04-05'
		WHEN 'Tue' THEN '2010-04-06'
		WHEN 'Wed' THEN '2010-04-07'
		WHEN 'Thu' THEN '2010-04-08'
		WHEN 'Fri' THEN '2010-04-09'
		WHEN 'Sat' THEN '2010-04-10'
		WHEN 'Sun' THEN '2010-04-11'
	END AS Date	
 FROM TC27
), AllSchedules AS (
	SELECT  SchID,Patient,Nurse,dy,St,En,DyNo,
			DATEADD(mi,CAST(SUBSTRING(St,3,2) AS INT) ,DATEADD(hour,CAST(SUBSTRING(St,1,2) AS INT),Date)) AS STime,
			CASE WHEN En<St THEN
				DATEADD(d,1,DATEADD(mi,CAST(SUBSTRING(En,3,2) AS INT) ,DATEADD(hour,CAST(SUBSTRING(En,1,2) AS INT),Date)) )
			ELSE 
				DATEADD(mi,CAST(SUBSTRING(En,3,2) AS INT) ,DATEADD(hour,CAST(SUBSTRING(En,1,2) AS INT),Date)) 
			END AS ETime
		FROM Dates
	), AllSchedulesWithRowNo AS (SELECT *,row_number() OVER (PARTITION BY Nurse ORDER BY STime,ETime) AS RowID FROM AllSchedules)
,Schedules AS(
	SELECT SchID,Patient,Nurse,dy,St,En,CAST('No' AS VARCHAR(3))  AS Overlapping ,stime,etime,RowID,STime as PrevStartTime,ETime as PrevEndTime,DyNo
	FROM AllSchedulesWithRowNo WHERE RowID=1
	UNION ALL
	SELECT  c2.SchID,c2.Patient,c2.Nurse,c2.dy,c2.St,c2.En,O.IsOverlapping  AS Overlapping ,c2.stime,c2.etime ,c2.RowID,
	CASE 
			WHEN O.IsOverlapping = 'Yes' THEN c1.PrevStartTime 
			ELSE c2.STime
		END,
		CASE 
			WHEN O.IsOverlapping = 'Yes' THEN c1.PrevEndTime 
			ELSE c2.ETime
		end,c2.DyNo
	FROM Schedules c1
	INNER JOIN AllSchedulesWithRowNo c2 ON c1.Nurse = c2.Nurse and c2.RowID = c1.RowID + 1
	CROSS APPLY (SELECT 	
				CASE 
					WHEN (c2.STime >= c1.PrevStartTime and c2.STime <  c1.PrevEndTime) or  ( c2.ETime >= c1.PrevStartTime and c2.ETime < c1.PrevStartTime) then 'Yes'
					ELSE 'No'
				END AS IsOverlapping)O
	) 
	SELECT SchID,Patient,Nurse,dy,St,En,Overlapping FROM Schedules
	ORDER BY Nurse,Dyno,St,En

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Aug  2 2010  2:06PM       39904           3       14492   14.776
  2 Aug  2 2010  2:16PM       39904           3       14492   14.667
  3 Aug  2 2010  2:26PM       39904           3       14430   14.643
  4 Aug  2 2010  2:36PM       39904           3       14462   14.777
  5 Aug  2 2010  2:46PM       39904           3       14290   14.746