[RESOLVED] Finding availability from a table of dates

phpSimon

Hi all

I have a table of bookings with 3 columns; itemID, startDate and endDate.

If there are a number of bookings in the table, each for differing periods and for different items (and some multiple bookings for the same item), I can find out when the next available date that ALL items are NOT booked by doing something like this (I'm sure this isn't the most elegant way to do it):

  SELECT endDate
    FROM bookings
ORDER BY endDate DESC
   LIMIT 1

What would be the easiest way to find out the earliest date from which all items are not booked, for a block of days?

I could do it with a couple of queries and some PHP (by doing the above, then querying again to see if any startDates or endDates exist between the first available date, and a selected end date) but I'd have thought there would be a more elegant way of doing it.

What I'm hoping to create is a function where providing a start and end date, and a duration in days, returns an array of 'blocks' between the provided dates, that are at least X days long, where no items are booked.

Thank you.

johanafm

phpSimon;10987983 wrote:
I can find out when the next available date that ALL items are NOT booked by doing something like this

That's not at all what you are doing. You are selecting the end dates for all bookings, which tells you absolutely nothing about when things are NOT booked. Well almost nothing. You do know that after the last end date, no items are booked, but that is not finding the next date (which happens to be the earliest date) when nothing is booked, that is finding the last such date.

Unless I'm misstaken, you'd need to recursively expand any period (start-end) with any overlapping period, until you have no more overlapping peroids, and from that take the first date outside of those periods.

For example, let's say you have bookings in the same month
1-3, 2-3, 2-5, 3-5, 4-6
8-10, 8-13, 12-14, 9-15
20-21, 21-23, 22-31

These rows would be "period-exapnded" to
1-6
8-15
20-31

which yields the answer 7. The same data structure would also be able to answer the question of what blocks of certain number of days exist.

But, if you only want to find out if a particular date cotains no bookings at all, that is a whole lot easier

SELECT COUNT(*) AS rows FROM bookings WHERE @some_date NOT BETWEEN start_date AND end_date

0 rows = no items booked that date. rows > 0, 1 or more items booked that date.

And the same goes for finding out if a particular date is within a block of free days

SELECT COUNT(*) AS rows FROM bookings WHERE
-- first day in the period can't be within some booking period
(@period_start NOT BETWEEN start_date AND end_date)
AND
-- same goes for last day in the period
(@period_end NOT BETWEEN start_date AND end_date)
AND
-- all days in between also can't be inside a booked period
(@period_start > end_date OR @period_end < start_date)

phpSimon

Of course, I can see you're exactly right. Takes a bit of lateral thinking!

I'll see if I can recursively expand all the booked periods, then examine the resulting data (probably through an array) to find a block of a given number of days.

Thanks for the tips so far!

johanafm

johanafm;10988040 wrote:
Unless I'm misstaken, you'd need to recursively expand any period (start-end) with any overlapping period

Actually, thinking about it, you should be able to do this using one-pass iteration, assuming you order the input ascending after start date. The algorithm is pretty much

empty array of "new dates" ND
get first date (use as "current period" CP), assign it to first element of ND
while there are more dates, check the next date and...
a. if its start is before CP-end, update CP end to latest of the two end dates
b. else, start a new period, i.e. put this period in a new element of ND

return ND

phpSimon

That makes sense, thank you.

I'll try that (I hadn't got as far as attempting to try the recursive expansion anyway).

phpSimon

Since starting this thread, I've changed the bookings table! I think I need to approach this in another way. The table now has 4 columns.

itemID, locationID, startDate, endDate

What I want to find is what combinations of itemID and locationID are NOT booked for a given date range.

I've been testing a theory like this:

SELECT itemID
     , locationID
  FROM bookings
 WHERE NOT EXISTS (
                  SELECT *
                    FROM bookings
                   WHERE ('$checkStart' BETWEEN startDate AND endDate
                      OR '$checkEnd' BETWEEN startDate AND endDate)
                  )

The logic here is: get all the bookings that overlap the start and end check dates I've provided - then return all the OTHER location and item IDs.

The subquery seems to work OK; it returns all the rows that are booked, but the result of the total query is empty, because there are no rows that are 'not' booked so there's nothing to return.

Is there a way to do this, other than in a series of queries and foreach loops like this?

Pseudo code:

/*
$itemID = array of all the itemID's
$locationID = array of all the locationID's
$date = first date in the range I want to check
$enddate = last date in the range I want to check

while($date <= $enddate){
for each ($itemID){
  for each ($locationID) {

SELECT * FROM bookings WHERE '$date' NOT BETWEEN startDate AND endDate
AND itemID = '$itemID' AND locationID = '$locationID';

if (mysql_num_rows == 0) { // this date not booked for this combination
$resultdatearray[] = array('$date' => '#'.'$locationID'.'#'.'$itemID'.'#');
}

  } // done all locations
  } // done all items
$date++;
} // done all dates
*/

Thank you

johanafm

Would it not be easier to simply get the items that are actually not booked to begin with

SELECT ... FROM ...
WHERE startdate NOT BETWEEN $checkStart AND $checkEnd
	AND enddate NOT BETWEEN $checkStart AND $checkEnd

phpSimon

Wow.

That is spot on and does exactly what I need, in one query!

Thank you!!!

phpSimon

Actually...

What this query does is return all the bookings in the table which are outside the range of dates I'm testing, but what I want to get is a list of all the itemIDs (from the items table) and all the locationIDs (from the locations table) where a combination of the two does not equal a booking that matches the date range I'm testing.

I think there must be a way of doing this in SQL using JOINS but I can't see it, yet. However, this thread is helping me get closer, thank you.

johanafm

Ah, I see. In that case I'd need to know what fields relate item, location and booking. For example, does each item have a loaction_id, or does each location have an item_id or does each booking have a location_id and a booking_id, or are any of the tables related by an "link table" to provide N:N (many to many) relations? The easiest way might be to provide all of the schema information for these tables.

I would assume the relation is that bookings have both item_id and location_id, while the others are not (directly) related. But if this is the case, I fail to see the purpose of selecting both items and locations, since this would mean that any item can go anywhere.

Either way, you will hve to construct the query differently depending on what relates to what.

phpSimon

You're exactly correct.

The items table includes an itemID, the locations table includes a locationID and there are no direct relations between the two tables, other than through the bookings table.

The bookings table contains itemID, locationID, startDate and endDate. In fact, the primary key is the combination of all 4; it's not possible to book the same item & location with the same dates. Additionally, any item booking cannot overlap dates within the same location. An item booking can overlap dates if the location is different, and a location can have multiple bookings of items with overlapping dates (or even the same dates) as long as the itemIDs are different.

I've refined the description of the question to:

"Select all possible itemID and locationID combinations, where that combination does NOT overlap a booking in the bookings table, with a given date range."

I hope that helps, and greatly appreciate your assistance.

phpSimon

Updated...

I found out how to select all possible combinations of locationID and itemID by doing this:

SELECT a.locationID, b.itemID
FROM locations a
JOIN items b
ORDER BY a.locationID, b.itemID

So the next step is to work out how to remove any combinations of locationID and itemID that exist in the bookings table for a given date range.

johanafm

Such a query implies that
1. An item is not a specific item, but rather several items of an item type, and that those items can be in several locations at once (do you check that all such items are not in use?)
2. Several items of the same type cannot be in the same place (e.g. you can only have one camera in one place)

Also note that while your last query is perfectly correct and valid, joins are usually used to join records that are somehow related on one or more fields (using the ON ... part of the join clause). As such, I'd recommend using FROM table1, table2 if you want the cartesian product (cross join) of two or more tables.

But if this is what you want to achieve

SELECT itemID, locationID, itemname, locationname
FROM item, location
WHERE (itemID, locationID) NOT IN
(
	SELECT itemID, locationID
	FROM bookings
	WHERE (startdate between @checkStart AND @checkEnd) OR
	(enddate between @checkStart AND @checkEnd)
)

phpSimon

That's correct; items aren't physical items; they can be in several locations at once and yes, items cannot be in the same place at the same time.

Thanks for the advice on the use of the comma operator rather than JOIN for cartesian products.

I've started testing the query you kindly created; I added the table names to the column names to clear some 'ambiguous' errors but otherwise it seems to work perfectly. I'll complete the testing soon as I can and set this thread as resolved.

Thank you again.

phpSimon

Yes, this query does exactly what I wanted.

Thanks again.