Solution to TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing By pp.pragnesh

--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--
--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

SELECT
	  Training
	, ClassRoom
	, Timing
	, SUBSTRING( 
				 ( CASE WHEN WkTotal & 1 = 1 THEN ',Mon' ELSE '' END
					+ CASE WHEN WkTotal & 2 = 2 THEN ',Tue' ELSE '' END
					+ CASE WHEN WkTotal & 4 = 4 THEN ',Wed' ELSE '' END
					+ CASE WHEN WkTotal & 8 = 8 THEN ',Thu' ELSE '' END
					+ CASE WHEN WkTotal & 16 = 16 THEN ',Fri' ELSE '' END
				  ), 2, 100 ) AS Schedule
FROM (
		SELECT 
			  Training
			, ClassRoom
			, StartTime + ' - ' + SUBSTRING( CONVERT( VARCHAR(16), CAST( '1900-01-01' AS DATETIME ) + STARTTIME + Duration, 120 ), 12, 5 ) AS Timing
			, Sum( WkCode ) AS WkTotal
		FROM TC26_TrainingInfo
			INNER JOIN (
							SELECT 'M' AS Wk, 'Mon' AS WkName, 1 AS WkCode
							UNION ALL SELECT 'T' AS Wk, 'Tue' AS WkName, 2 AS WkCode
							UNION ALL SELECT 'W' AS Wk, 'Wed' AS WkName, 4 AS WkCode
							UNION ALL SELECT 'TH' AS Wk, 'Thu' AS WkName, 8 AS WkCode
							UNION ALL SELECT 'F' AS Wk, 'Fri' AS WkName, 16 AS WkCode
						) AS WkDet ON WkDet.Wk = TC26_TrainingInfo.Wk
		GROUP BY Training
			, ClassRoom
			, StartTime + ' - ' + SUBSTRING( CONVERT( VARCHAR(16), CAST( '1900-01-01' AS DATETIME ) + StartTime + Duration, 120 ), 12, 5 )
	) AS A

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jul 22 2010 10:57PM          89           0          47    0.110
  2 Jul 22 2010 11:13PM          89           0          62    0.126
  3 Jul 22 2010 11:28PM          89           0          46    0.104
  4 Jul 22 2010 11:44PM          89           0          47    0.091
  5 Jul 22 2010 11:59PM          89           0          47    0.123