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