CODEWITHSUNDEEP
sql
JK36
JK36

Reputation: 853

Find whether something booked in a time range

I'm having difficulty trying to find whether a room is booked within a certain time range. I store the starttime and endtime of a room in a table called periodInformation.

I can't quite get my head around time/range in sql. I need my query below to interrogate pi.StartTime and pi.EndTime and see whether the room is booked. In my example below user has typed in they want a room from 08:20 to 09:20. I want to ensure whilst searching pi.StartTime and pi.EndTime I also get results back if room A1 was booked from 8:10 - 8:30 or 8:10-9:30 etc.

I think I've selected the appropriate joins, can only find examples of joining two tables, here I'm joining 4 tables to figure out whether a room is booked or not.

ExecuteSQL ( "
SELECT lr.pk_LessonRoomID
FROM LESSONROOM AS lr
LEFT JOIN Lesson AS l ON lr.fk_LessonID = l.pk_LessonID
LEFT JOIN PeriodInformation AS pi ON l.fk_PeriodListID = pi.fk_PeriodListID
LEFT JOIN PeriodList AS pl ON pi.fk_PeriodListID = pl.pk_PeriodListID
WHERE lr.fk_RoomID = ? AND pl.DayShort = ? AND pi.StartTime >=? AND pi.EndTime <=?" ;

 ""; 

"" ; 

"A1" ; "Mon" ; "08:20" ; "09:20")

Upvotes: 0

Views: 46

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

For a room to be BOOKED between 8:20 and 9:20, it would have to have a lesson that ENDS after 8:20, and STARTS before 9:20.

So if you change your last two conditions to this:

AND pi.EndTime >=? AND pi.StartTime <=?

That should cover it.

Upvotes: 1

Related Questions