CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Billa
Billa

Reputation: 5266

Combine single date record with multiple time slot

Below are the tables used to populate the event slots.

Event Table

EventID | Name         | EventDateTime(SMALLDATETIME)       
--------+--------------+-----------------------------
   1    | Tech Session | 2014-02-21 00:00:00 
   2    | Job Session  | 2014-02-25 00:00:00

Slots Table

SlotID  | EventId| StartTime(TIME)  |  EndTime(TIME)   |
--------+--------------+---------------------+----------
   1    | 1      | 10:00:00.0000000 | 10:45:00.0000000 |
   2    | 1      | 10:45:00.0000000 | 11:30:00.0000000 |
   3    | 1      | 11:30:00.0000000 | 12:15:00.0000000 |
   4    | 1      | 12:15:00.0000000 | 13:00:00.0000000 |
   5    | 2      | 11:00:00.0000000 | 13:00:00.0000000 |
   6    | 2      | 13:00:00.0000000 | 15:00:00.0000000 |

Expected AvailableSlots: How do i query the to get the below result?

Name         | StartDateTime       |  EndDateTime        |
--------+--------------+---------------------+------------
Tech Session | 2014-02-21 10:00:00 | 2014-02-21 10:45:00 |
Tech Session | 2014-02-21 10:45:00 | 2014-02-21 11:30:00 |
Tech Session | 2014-02-21 11:30:00 | 2014-02-21 12:15:00 |
Tech Session | 2014-02-21 12:15:00 | 2014-02-21 13:00:00 |
Job Session  | 2014-02-25 11:00:00 | 2014-02-21 13:00:00 |
Job Session  | 2014-02-25 13:00:00 | 2014-02-21 15:00:00 |

Upvotes: 0

Views: 266

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

You may also want to consider the posibility of dayshift between [StartTime] and [EndTime]. That would render my answer invalid

SELECT a.name, 
       a.eventdatetime + b.starttime StartDateTime, 
       a.eventdatetime + b.endtime   Enddatetime 
FROM   event a 
       JOIN slot b 
         ON a.eventid = b.eventid

Upvotes: 2

TechDo
TechDo

Reputation: 18629

Please try:

SELECT 
    Name, 
    DATEADD(SECOND, DATEDIFF(SECOND, 0, StartTime), EventDateTime) StartDateTime, 
    DATEADD(SECOND, DATEDIFF(SECOND, 0, EndTime), EventDateTime) EndDateTime
FROM EventTable a INNER JOIN SlotsTable b ON a.EventId=b.EventId

Upvotes: 2

Related Questions