>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

 

Advertisements

Posted on February 23, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: