TSQL Finding Overlapping Hours

Question

When two tables are given

Employee Table

EmpID  Name
1      Jon   
2      Smith  
3      Dana 
4      Nancy

Lab Table

EmpID   StartTime   EndTime    Date            LabID
1       10:00 AM    12:15 PM   01/JAN/2000     Lab I 
1       11:00 AM    14:15 PM   01/JAN/2000     Lab II 
1       16:30 PM    18:30 PM   01/JAN/2000     Lab I

2      10:00 AM    12:10 PM    01/JAN/2000     Lab I

From the given details ,I have to find out the overlapping hours,and non overlapping hours of each employee on each date. (StartTime and EndTime are of type varchar).

The expected output is 
-------------------------------------------------------------------------------
EmpID| Name|  Overlapping                 | Non-Overlapping      |    Date 
              Period                        Period  
-------------------------------------------------------------------------------
1      Jon | 10:00 AM  to 12:15 PM       |16:30 PM to   18:30 PM | 01/JAN/2000   
           |  AND                        |                       | 
           |  11:00 AM  to 14:15 PM      |                       |
           |  AND ...(If any)            |                       | 
--------------------------------------------------------------------------------

2    Smith|      NULL                    | 10:00 AM to 12:10 PM  |01/JAN/2000
-------------------------------------------------------------------------------- 

Please help me to bring such output using TSQL(SQL Server 2005/2008).

Answer 1

First, you should probably consider using a DateTime field to store the StartTime and EndTime, and thus make calculations easier, and remove the need for the Date field.

SELECT t1.EmpID, 
       t1.StartTime, 
       t1.EndTime,
       t2.StartTime
       t2.EndTime,
FROM lab t1
LEFT OUTER JOIN lab t2
   ON t2.StartTime BETWEEN t1.StartTime AND t1.EndTime
      AND t2.EmpID = t1.EmpID
ORDER BY t1.EmpID,
         t1.StartTime,
         t2.StartTime

That won't get you the EXACT format you have listed, but it's close. You should end up with:

| EmpID|   Name|    Normal Period    | Overlapping Period  |
------------------------------------------------------------
| 1    |   Jon | 10:00 AM | 12:15 PM | 11:00 AM | 02:15 PM |
------------------------------------------------------------
| 2    | Smith | 10:00 AM | 12:10 PM | NULL     | NULL     |
------------------------------------------------------------

Each overlapped period within a normal period would show up in a new row, but any period with no overlaps would have only one row. You could easily concatenate the fields if you wanted specifically the "xx:xx xx to xx:xx xx" format. Hope this helps you some.