2

I am trying to compare 2 given time fields with two time fields, StartDateTime and EndDateTime respectively.

enter image description here

From my Resources, I have 5 items of different resource types and I have to do a check whether my StartDateTime and EndDateTime does not fall between the values in my database – If so, the Resource Item is available to be used.

enter image description hereenter image description here

enter image description here

From the sample data above, I wanted to do a query to select ALL resources of the selected resource type, and not show the resource items that are being used:

SELECT a.ResourceID 
FROM Resources AS a 
LEFT OUTER JOIN Bookings AS b 
ON a.ResourceID = b.ResourceID 
AND b.Status IS NULL 
WHERE a.ResourceTypeID = (SELECT c.ResourceTypeID FROM ResourceType AS c INNER JOIN Resources AS d ON c.ResourceTypeID = d.ResourceTypeID WHERE c.ResourceType = 'Coffee Machines' LIMIT 1) 
AND NOT a.ResourceID IN (SELECT ResourceID FROM Bookings WHERE DATE(StartDateTime) = '2017-07-15' AND Status IS NULL AND ((TIME('11:00:00') >= TIME(StartDateTime) OR TIME('11:00:00') <= TIME(EndDateTime)) AND (TIME('12:00:00') >= TIME(StartDateTime) OR TIME('12:00:00') <= TIME(EndDateTime))))

The subquery in line 7 is to query for resourceIDs that are being used, and with the AND NOT Clause, the query will only show the ones that are available. But the subquery logic is wrong... I am getting this result:

enter image description here

My given time fields do not fall between the time fields in the database, yet CM-01 and CM-03 is not shown. Can anyone please help me solve this logic?

1
  • Something like WHERE foo BETWEEN start AND end
    – Rick James
    Jul 14 '17 at 22:06
2

Actually, I think you're wrong - there are conflicts for CM-01 and CM-03, based on the query and the data shown.

Your data shows bookings for:

  • CM-01 on 2017-07-15 starting at 10:30 and ending at 11:00
  • CM-03 on 2017-07-15 starting at 10:35 and ending at 11:05

(No AM/PM shown, so assuming all times are military (00:00 to 23:59).

You're checking if resources are free between 11:00 and 12:00 on 2017-07-15.

So, CM-03 should trivially be unavailable, as there's a five minute period where the current booking overlaps with the desired (11 - 12) new booking.

Based on the intent of the rules you've laid out, I'd say CM-01 should also be unavailable. Your comparing the starts and ends of the two times inclusively. That would imply that, if something's booked for 10:30 to 11:00, and is wanted for 11:00 to 12:00, then it shouldn't be available, because the end of the existing booking is the same as the start of the new booking. If you want to allow back-to-back booking, you need to tweak the logic.

However, if we look at the query, I do think there is a problem. Let's say there was a booking for CM-02 on 2017-07-15 from 9:00 to 10:00 (with a NULL Status). That should clearly not prevent a new booking from 11:00 to 12:00 from being added.

However, when we look at your query to find IDs that should be ignored (reformatted for readability):

SELECT ResourceID
  FROM Bookings
 WHERE DATE(StartDateTime) = '2017-07-15'
   AND Status IS NULL
   AND (    (   TIME('11:00:00') >= TIME(StartDateTime)
             OR TIME('11:00:00') <= TIME(EndDateTime)
            )
        AND (   TIME('12:00:00') >= TIME(StartDateTime)
             OR TIME('12:00:00') <= TIME(EndDateTime)
            )
       )

and consider our 9-10 booking for CM-02, here's what happens:

  • DATE(StartDateTime) = '2017-07-15' - This is true.
  • Status IS NULL - So is this.
  • Checking the start time for the desired (11-12) booking against the existing (9-10) booking:
    • TIME('11:00:00') >= TIME(StartDateTime) - "11:00:00" is greater than "9:00:00", so true.
    • We can skip the second check, as this branch is already true.
  • Checking the end time for the desired (11-12) booking against the existing (9-10) booking:
    • TIME('12:00:00') >= TIME(StartDateTime) - "12:00:00" is greater than "9:00:00", so true.
    • We can skip the second check, as this branch is already true.
  • So, all is true - and we apparently have a conflict (except we shouldn't).

Feel free to confirm the above by adding such a record to your test data.

I've worked through this before, and there are two things you need to check:

  • Does time period A start before time period B ends?
  • Does time period A end after time period B starts?

If both are true, you have an issue.

Note - this does assume your start and end times are in the correct order. If either time period's start time comes before its end time, this won't work.

So, rewrite the query as:

SELECT ResourceID
  FROM Bookings
 WHERE Status IS NULL
   AND CAST('2017-07-15 11:00:00' as datetime) <= EndDateTime
   AND CAST('2017-07-15 12:00:00' as datetime) >= StartDateTime

Let's compare that to the booking I created, and to the bookings you had for the desired 11-12 booking:

  • CM-02, 2017-07-15 9:00 - 10:00
    • 11:00 (desired) <= 10:00 (existing) = FALSE
    • 12:00 (desired) >= 9:00 (existing) = TRUE
    • Result - FALSE, so OK to list for desired booking.
  • CM-03, 2017-07-15 10:35 - 11:05
    • 11:00 (desired) <= 11:05 (existing) = TRUE
    • 12:00 (desired) >= 10:35 (existing) = TRUE
    • Result - TRUE, so not available for desired booking.
  • CM-01, 2017-07-15 10:30 - 11:00
    • 11:00 (desired) <= 11:00 (existing) = TRUE
    • 12:00 (desired) >= 10:30 (existing) = TRUE
    • Result - TRUE, so not available for desired booking.

If you want to allow back-to-back bookings (like for CM-01, 10:30 - 11:00 and 11:00 - 12:00), then change the two comparisons to be < and >, not <= and >=

With that change:

  • CM-01, 2017-07-15 10:30 - 11:00
    • 11:00 (desired) < 11:00 (existing) = FALSE
    • 12:00 (desired) > 10:30 (existing) = TRUE
    • Result - FALSE, so OK to list for desired booking.

NOTE: Code is untested.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.