CODEWITHSUNDEEP
sql
Chris Devine
Chris Devine

Reputation: 67

SQL Free Rooms between Dates and at a specific Time

I have read a few articles about finding available rooms, however I cannot get my version to work.

Can somebody have a look at my SQL and maybe shed some light on my problem please.

Tables

RoomTBL(RoomNo, RoomName)
LessonsTBL(LessonID, RoomID, StaffID, TimeSlot)
DatesTBL(DateID, LessonID, startDate, endDate)

I want to find available rooms on a particular TimeSlot, between the StartDate and endDate. (Its a block booking system for a school)

I have tried

SELECT RoomNumber 
FROM RoomTBL 
WHERE RoomNumber NOT IN (SELECT RoomNumber 
                   FROM LessonsTBL LT 
                   JOIN DatesTBL DT ON LT.LessonID = DT.LessonID  
                   Where(DT.startDate <= '2016-03-21' AND DT.endDate >= '2016-03-21') 
                   OR (DT.startDate < '2016-04-21' AND DT.endDate >= '2016-04-21') 
                   OR ('2016-03-21' <= DT.startDate AND '2016-04-21' >= DT.startDate)
                   AND LT.TimeSlot = 1)

but it doesnt return any rooms (I have 6 rooms not in use at all) I hope I have explained the issue correctly.

No doubt I am missing something, Thanks.

Upvotes: 0

Views: 179

Answers (1)

WhatEva
WhatEva

Reputation: 36

I think I have understood what you are trying to do, but you are trying to select RoomNumber (which isn't a field in any of the tables) from LessonsTBL. I have made a lot of assumptions in this answer, so please clarify if I guessed incorrectly.

You seem to be looking for a room which is not booked for the first timeslot on any days between '2016-03-21' AND '2016-04-21'

SELECT RoomID
FROM LessonsTBL
WHERE RoomID NOT IN
 (SELECT RoomID 
  FROM LessonsTBL LT JOIN DatesTBL ON LT.LessonID = DT.LessonID
  WHERE LT.TimeSlot = 1 AND (DT.startDate BETWEEN '2016-03-21' AND '2016-04-21') OR (DT.endDate BETWEEN '2016-03-21' AND '2016-04-21')

Unless you need the RoomName, you don't need the RoomTBL.

Upvotes: 1

Related Questions