问Sql查询变更

EN

Declare @Date char(8) = '20141013'
;
WITH cte as
(
    SELECT *
    FROM -- use your table name instead of the VALUES construct
    (VALUES
    ('09:00:00','12:30:00' ,'7-3', '20140919'),
    ('15:00:00','17:00:00' ,'7-2', '20141013'),
    ('14:00:00','16:00:00' ,'7-3', '20140919')) x(EventStart , EventEnd,Rooms, DayStarts)
), cte_Days_Rooms AS
-- get a cartesian product for the day specified and all rooms as well as the start and end time     to compare against
(
    SELECT y.EventStart,y.EventEnd, x.rooms,a.DayStarts FROM 
    (SELECT @Date DayStarts) a
    CROSS JOIN
    (SELECT DISTINCT Rooms FROM cte)x
    CROSS JOIN
    (SELECT '09:00:00' EventStart,'09:00:00' EventEnd UNION ALL
     SELECT '22:00:00' EventStart,'22:00:00' EventEnd) y        
 ), cte_1 AS
-- Merge the original data an the "base data"
(
    SELECT * FROM cte WHERE DayStarts=@Date
    UNION ALL
    SELECT * FROM cte_Days_Rooms
), cte_2 as
-- use the ROW_NUMBER() approach to sort the data
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DayStarts, Rooms ORDER BY EventStart) as pos
    FROM cte_1
)
-- final query: self join with an offest of one row, eliminating duplicate rows if a room is booked starting 9:00 or ending 22:00
SELECT c2a.DayStarts, c2a.Rooms , c2a.EventEnd, c2b.EventStart 
FROM cte_2 c2a
INNER JOIN cte_2 c2b on c2a.DayStarts = c2b.DayStarts AND c2a.Rooms =c2b.Rooms AND c2a.pos = c2b.pos -1
WHERE c2a.EventEnd <> c2b.EventStart
ORDER BY c2a.DayStarts, c2a.Rooms

Event       EventStart  EventEnd    Days                Rooms   DayStarts
CISC 3660   09:00:00    12:30:00    Monday              7-3     9/19/2014   
MATH 2501   15:00:00    17:00:00    Monday:Wednesday    7-2     10/13/2014  
CISC 1110   14:00:00    16:00:00    Monday              7-3     9/19/2014