>TSQL Challenge 49: Identify overlapping time sheet entries of field technicians

>

I am a big fan of http://beyondrelational.com/and specially its challenges. Recent quiz (TSQL Challenge NO.49) was an interesting and very useful for DBAs and developers. Everyone must visit and try this challenge.

 http://beyondrelational.com/blogs/tc/archive/2011/02/07/tsql-challenge-49-identify-overlapping-time-sheet-entries-of-field-technicians.aspx

 Here is an idea to solve this challenge.But it would be better if you try it yourself. (Base table structure, data and expected result can be found on above mentioned link)

SELECT  BaseTable.Technician,

        BaseTable.Grade,

        CASE WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime

                    AND BaseTable.StartTime <= LeftOuterTable.EndTime

                   

                  ) THEN BaseTable.StartTime

             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime

                    AND BaseTable.EndTime <= LeftOuterTable.EndTime

                    AND BaseTable.EndTime >= LeftOuterTable.StartTime

                  ) THEN LeftOuterTable.StartTime

             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime

                    AND BaseTable.EndTime >= LeftOuterTable.EndTime

                  ) THEN LeftOuterTable.StartTime

        END AS CStartTime,

        CASE WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime

                    AND BaseTable.StartTime <= LeftOuterTable.EndTime

                    AND BaseTable.EndTime <=LeftOuterTable.EndTime

                  ) THEN BaseTable.EndTime

              WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime

                    AND BaseTable.StartTime <= LeftOuterTable.EndTime

                    AND BaseTable.EndTime >=LeftOuterTable.EndTime

                  ) THEN LeftOuterTable.EndTime

              WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime

                    AND BaseTable.EndTime <= LeftOuterTable.EndTime

                    AND BaseTable.EndTime >= LeftOuterTable.StartTime

                  ) THEN BaseTable.EndTime

             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime

                    AND BaseTable.EndTime >= LeftOuterTable.EndTime

                  ) THEN LeftOuterTable.EndTime

        END AS CEndTime,

        LeftOuterTable.Technician AS OverlapedWith

FROM    TC49 AS BaseTable

        LEFT OUTER JOIN TC49 LeftOuterTable ON ( BaseTable.StartTime >= LeftOuterTable.StartTime

AND BaseTable.StartTime <= LeftOuterTable.EndTime

OR ( BaseTable.EndTime >= LeftOuterTable.StartTime

AND BaseTable.EndTime <= LeftOuterTable.EndTime

) OR ( BaseTable.StartTime <= LeftOuterTable.StartTime

AND BaseTable.EndTime >= LeftOuterTable.EndTime

)

WHERE   BaseTable.Technician <> LeftOuterTable.Technician

ORDER BY Grade