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

/* andrewd_smith_tsqlchallenge_26_v2.sql */
/* To remove duplicate schedules, uncomment DISTINCT in the aggregate expression. */

SELECT 
   AGG.Training, 
   AGG.ClassRoom AS Classroom,
   AGG.StartTime + ' - ' + CONVERT(char(5), CONVERT(datetime, AGG.StartTime) + CONVERT(datetime, AGG.Duration), 108) AS Timing,
   WKD.Schedule
FROM (
   SELECT 
      Training, 
      ClassRoom, 
      StartTime, 
      Duration, 
      SUM( /* DISTINCT */ CASE Wk 
         WHEN 'M' THEN 1
         WHEN 'T' THEN 2
         WHEN 'W' THEN 4
         WHEN 'TH' THEN 8
         WHEN 'F' THEN 16
         ELSE 0 END
      ) AS WeekdayBits
   FROM dbo.TC26_TrainingInfo
   GROUP BY Training, ClassRoom, StartTime, Duration
) AGG
INNER JOIN (
   SELECT 0, ''
   UNION ALL SELECT 1, 'Mon'
   UNION ALL SELECT 2, 'Tue'
   UNION ALL SELECT 3, 'Mon,Tue'
   UNION ALL SELECT 4, 'Wed'
   UNION ALL SELECT 5, 'Mon,Wed'
   UNION ALL SELECT 6, 'Tue,Wed'
   UNION ALL SELECT 7, 'Mon,Tue,Wed'
   UNION ALL SELECT 8, 'Thu'
   UNION ALL SELECT 9, 'Mon,Thu'
   UNION ALL SELECT 10, 'Tue,Thu'
   UNION ALL SELECT 11, 'Mon,Tue,Thu'
   UNION ALL SELECT 12, 'Wed,Thu'
   UNION ALL SELECT 13, 'Mon,Wed,Thu'
   UNION ALL SELECT 14, 'Tue,Wed,Thu'
   UNION ALL SELECT 15, 'Mon,Tue,Wed,Thu'
   UNION ALL SELECT 16, 'Fri'
   UNION ALL SELECT 17, 'Mon,Fri'
   UNION ALL SELECT 18, 'Tue,Fri'
   UNION ALL SELECT 19, 'Mon,Tue,Fri'
   UNION ALL SELECT 20, 'Wed,Fri'
   UNION ALL SELECT 21, 'Mon,Wed,Fri'
   UNION ALL SELECT 22, 'Tue,Wed,Fri'
   UNION ALL SELECT 23, 'Mon,Tue,Wed,Fri'
   UNION ALL SELECT 24, 'Thu,Fri'
   UNION ALL SELECT 25, 'Mon,Thu,Fri'
   UNION ALL SELECT 26, 'Tue,Thu,Fri'
   UNION ALL SELECT 27, 'Mon,Tue,Thu,Fri'
   UNION ALL SELECT 28, 'Wed,Thu,Fri'
   UNION ALL SELECT 29, 'Mon,Wed,Thu,Fri'
   UNION ALL SELECT 30, 'Tue,Wed,Thu,Fri'
   UNION ALL SELECT 31, 'Mon,Tue,Wed,Thu,Fri'
) WKD(WeekdayBits, Schedule) 
   ON (AGG.WeekdayBits = WKD.WeekdayBits)

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jul 22 2010 10:45PM          89           0          16    0.076
  2 Jul 22 2010 11:01PM          89           0          47    0.091
  3 Jul 22 2010 11:16PM          89           0          62    0.083
  4 Jul 22 2010 11:32PM          89           0          31    0.085
  5 Jul 22 2010 11:47PM          89           0          16    0.094