Query to find out entries where dates don't overlap

Question

Can anyone help me create a query which will populate a list of DJs who are not already booked in.

My user will select a start date (and time), and an end date (and time) - and then click a button to select a DJ.

I only want those DJs which are available between those time slots to appear in the list.

Here are the two tables which are involved

all I need in the listbox is the DJ Number, and the DJ Name

So far I have this... but it isn't working:

SELECT tblDJ.DJ_No AS [DJ ID], tblDJ.DJ_Name AS Name FROM tblDJ
WHERE (((tblDJ.[DJ_No]) Not In
(SELECT tblBooking.[FK_DJ_No]
FROM tblBooking
WHERE ( (tblBooking.End_Date) >= 01-04-2020 19:30:00 )))) ....etc....

I'm just entering a date in here for now, but obviously it will be stored in a variable once implemented.

Thanks

Hi. well that's all the SQL code I have so far. Minus the '......etc...... if I run that I get 'syntax error in query expression' (which seems to stop if I remove the time (19:30:00)..... I would expect it to exclude DJ D00057 because they have a booking where the end date is later than 01/04/2020, but it doesn't make any difference what date I put in. Sorry for my vagueness — Mitch, Mar 07 '16 at 20:01

score 1 · Answer 1 · answered Mar 07 '16 at 20:03

The date in the SQL needs to be wrapped between two # in order for MS-Access to recognize it as a date:

select *
from tblDJ
where DJ_No not in
    (
    select FK_DJ_No 
    from tblBooking 
    where End_Date >= #2020-04-01 19:30:00#
    )

Other than that you query will work.

score 1 · Accepted Answer · answered Mar 07 '16 at 20:13

1

Implementing OVERLAPS of two intervals would look like:

1st_start_date <= 2nd_end_date and 1st_end_date >= 2nd_start_date

where 1st and 2nd values are markers of different events.

You could use that logic in combination with NOT EXISTS to discard those djs that are unavailable at a given time:

select dj_no, dj_name
from tbldj d
where not exists (
    select 1
    from tblbooking b
    where b.fk_dj_no = d.dj_no
      and b.start_date <= #END DATE#
      and b.end_date >= #START DATE#
  )

You just need to replace #START DATE# and #END DATE# with your values.

This does work because there are following assumptions:

Start date of the first event is prior to end date of that event
Start date of the second event is prior to end date of that event

Which seems logical, right?

answered Mar 07 '16 at 20:13

Kamil Gosciminski

16,547
8
49
72

Thanks - this is the closest thing I have had so far -- however, I can't go with those assumptions. - say a booking exists for dj no.1 starting 2001/01/01 20:00 and ending 2001/01/02 at 01:00 - I need it to not show DJ no.1 as available if someone tries to make a booking starting 2001/01/01 - 19:00 and ending 2001/01/01 23:30. How could I achieve this? – Mitch Mar 07 '16 at 22:42
If there is a way for an event to be starting after it finishes, there are some serious problems in the data validation. – Kamil Gosciminski Mar 07 '16 at 22:44
No - I have validation to prevent that - but somebody could book a new event which has a later start date than an existing booking (even if just by a few minutes) - but still overlaps - (if you see what I mean?) but the above code seems to allow that to happen? – Mitch Mar 07 '16 at 22:52
No, it doesn't. Your event to be booked start date must be before the end date of existing bookings, so this nullifies the reservation. – Kamil Gosciminski Mar 09 '16 at 19:06

Query to find out entries where dates don't overlap

2 Answers2