Wednesday, February 23, 2011

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

 

No comments:

Post a Comment

All suggestions are welcome