Jul 13  

Sometimes this is me

CREATE TABLE Halls 
(
	hallId				INT    IDENTITY ( 1 , 1 ),
	hallName			NVARCHAR(50),
	hallStartingTime	INT, -- 11 AM will be stored as 660 and 2 PM will be stored as 840
	hallClosingTime		INT, -- same comment for the above column
	capacity			INT
)

CREATE TABLE Hallbooking 
(
  hallBookingId INT    IDENTITY ( 1 , 1 ),
  hallId        INT,
  startDatetime DATETIME,
  endDatetime   DATETIME
)

-- halls

INSERT INTO Halls VALUES ('Hall 1', 540, 1320, 500) --available from 9 AM till 10 PM

INSERT INTO Halls VALUES ('Hall 2', 840, 1350, 200) --available from 2 PM till 10:30 PM

INSERT INTO Halls VALUES ('Hall3', 450, 1410, 400) --available from 7:30 AM till 11:30 PM

--bookings for 10/26
INSERT INTO Hallbooking VALUES (1, '2009-10-26 11:00:00', '2009-10-26 11:30:00')

INSERT INTO Hallbooking VALUES (2, '2009-10-26 16:15:00', '2009-10-26 19:30:00')

INSERT INTO Hallbooking VALUES (1, '2009-10-26 12:30:00', '2009-10-26 14:00:00')

INSERT INTO Hallbooking VALUES (1, '2009-10-26 16:00:00', '2009-10-26 18:00:00')

INSERT INTO Hallbooking VALUES (2, '2009-10-26 19:30:00', '2009-10-26 21:15:00') 

--bookings for 10/28
INSERT INTO Hallbooking VALUES (1, '2009-10-28 10:00:00', '2009-10-28 11:30:00')

INSERT INTO Hallbooking VALUES (2, '2009-10-28 18:15:00', '2009-10-28 19:00:00')

INSERT INTO Hallbooking VALUES (1, '2009-10-28 14:05:00', '2009-10-28 16:10:00')

INSERT INTO Hallbooking VALUES (1, '2009-10-28 18:30:00', '2009-10-28 20:00:00')

INSERT INTO Hallbooking VALUES (2, '2009-10-28 21:30:00', '2009-10-28 22:15:00') 



-- SAMPLE OUT PUT DATA

hallId      startDatetime              endDatetime
----------- -----------------------    -----------------------
1           2009-10-26 9:00:00.000    2009-10-26 11:00:00.000	--from opening time till first booking
1           2009-10-26 11:30:00.000    2009-10-26 12:30:00.000	--from first bookings end time till next bookings starting time
1           2009-10-26 14:00:00.000    2009-10-26 16:00:00.000	
1           2009-10-26 18:00:00.000    2009-10-26 22:00:00.000	-- from last booking time till Hall closing time

1           2009-10-28 9:00:00.000    2009-10-28 10:00:00.000	--from opening time till first booking
1           2009-10-28 11:30:00.000    2009-10-28 14:05:00.000
1           2009-10-28 16:10:00.000    2009-10-28 18:30:00.000
1           2009-10-28 20:00:00.000    2009-10-28 22:00:00.000	-- from last booking time for the Day till Hall closing time 

2           2009-10-26 14:00:00.000    2009-10-26 16:15:00.000	-- from opening time till first booking
2           2009-10-26 21:15:00.000    2009-10-26 22:30:00.000	-- from last booking time for the Day till Hall closing time 

2           2009-10-28 14:00:00.000    2009-10-28 18:15:00.000	-- from opening time till first booking
2           2009-10-28 19:00:00.000    2009-10-28 21:30:00.000
2           2009-10-28 22:15:00.000    2009-10-28 22:30:00.000	-- from last booking time for the Day till Hall closing time 

select H.hallId, T.N_Date, DATEADD(n, H.hallStartingTime, T.N_Date) Avail_From, ISNULL(MIN(B.startDatetime), DATEADD(n, H.hallClosingTime, T.N_Date) ) AS Avail_To
-- from beginning of the day to the next appointment
from Halls H
	INNER JOIN dbo.Tally T ON T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0) 
	left join Hallbooking B ON H.hallId = B.hallId AND B.startDatetime > T.N_Date AND B.startDatetime < T.N_Date+1 
		AND B.startDatetime > DATEADD(n, H.hallStartingTime, T.N_Date)
GROUP BY H.hallId, H.hallClosingTime, H.hallStartingTime, T.N_Date
UNION 
select H.hallId, T.N_Date, ISNULL(MAX(B.endDatetime), DATEADD(n, H.hallStartingTime, T.N_Date)) AS  Avail_From, DATEADD(n, H.hallClosingTime, T.N_Date) Avail_To
-- from the last appointment to end of the day 
from Halls H
	INNER JOIN dbo.Tally T ON T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0) 
	left join Hallbooking B ON H.hallId = B.hallId AND B.endDatetime > T.N_Date AND B.endDatetime < T.N_Date+1 
		AND B.endDatetime < DATEADD(n, H.hallClosingTime, T.N_Date)
GROUP BY H.hallId, H.hallStartingTime, H.hallClosingTime, T.N_Date
UNION
select B1.hallId, T.N_Date, B1.endDatetime AS  Avail_From, MIN(B2.startDatetime) Avail_To
-- slots between appointments 
from dbo.Tally T  
	INNER join Hallbooking B1 ON B1.endDatetime > T.N_Date AND B1.endDatetime < T.N_Date+1 
	INNER JOIN Hallbooking B2 ON B2.hallId = B1.hallId AND B2.startDatetime > B1.endDatetime AND B2.endDatetime < T.N_Date+1 
WHERE T.N_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())/7*7, 0) AND T.N_Date < DATEADD(dd, (DATEDIFF(dd, 0, GETDATE())/7+1)*7, 0) 
GROUP BY B1.hallId, B1.endDatetime, T.N_Date
ORDER BY H.hallId, Avail_From, Avail_To