MySQL logic for comparing two tables and calculating the SUM of entries in one

[SF23103]

I am looking for a little help on the direction to go with the logic for a MySQL query.  I'm not looking for someone to program it, just point me in the right direction for the logic.

 

1)  I have a table that contains available reservation dates.  It is populated with several dates.  There are two reservation times per day, 12:00 and 12:30.  Dates are in date format and stored correctly, but listed below as text for example.   See Table 1 below. 

 

2)  I have another table that contains reservations.  This is populated when someone completes a form for the registration.  Dates are stored in the same correct date format, but listed as text below.  See Table 2 Below

 

I would like to query the database and Loop through a list of all available dates/times.  There are 80 reservations per day, and the times do not matter.  For example, "The SUM of January 1, 2017 12:00 AND January 1, 2017 12:30 is less than or equal to 80" would echo both January 1, 2107 12:00 AND January 1, 2017 12:30.

 

I am currently just looping through the list from the dates table based on another column that is "available" or "not available" but that requires someone to go in and modify it manually.  I want it to remove the dates automatically when the number of reservations is greater than 80 for that day.

 

--------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

Table 1 - Dates (dates are in date format, but just written in text for this example)

 

ID | DATE

1  | January 1, 2017 12:00

2  | Date 2:  January 1, 2017 12:30 

3  |January 2, 2017 12:00

4  |January 2, 2017 12:30

5  |January 3, 2017 12:00

6  |January 3, 2017 12:30

... and so on.

 

 

Table 2 - Reservations (Here, people have made reservations and their reservation is saved in this table).

Reservation# | Date

ABC123        |  January 1, 2017 12:00

ABC456        |  January 1, 2017 12:00

ABC667        | January 1, 2017 12:00

ABC777        | January 2, 2017 12:30

etc                    etc

 

[requinix]

There are 80 reservations per day, and the times do not matter.

So SUM(number of reservations) GROUP BY DATE(date)

 

For example, "The SUM of January 1, 2017 12:00 AND January 1, 2017 12:30 is less than or equal to 80" would echo both January 1, 2107 12:00 AND January 1, 2017 12:30.

Given what you said above, why do the times matter? Do they not matter, and in this example where the date only has two times you're just mentioning them with the date?

For example, "The SUM of January 1, 2017 is less than or equal to 80" would echo both January 1, 2107 12:00 AND January 1, 2017 12:30.

 

One possible improvement would be to relate reservations to date IDs, not to the dates themselves.

Reservation# | Date
ABC123       | 1
ABC456       | 1
ABC667       | 1
ABC777       | 2
etc          | etc

It could help with the JOINs or subqueries you will inevitably do.

[SF23103]

Thank you for your help,

 

It is a little strange, I know.  The client wants people to be able to reserve one of two times on one date.  Instead of the obvious: having a max number of reservations for each of the two times, they want to max it out by number of the total reservations for that day.  Doesn't make sense to me, but that's what they want.  I guess what I should be doing, is querying the database for the dates, but ignoring the times?

 

I also forgot to mention that the Reservations table has a column for the number of people in the party for that reservation.

 

I like the idea of joining based on the date ID's, not the actual dates.. that may make it easier.  I'll play around with that.

[requinix]

Another suggestion is to break the dates into separate date and time components. That makes matching up dates and reservations more efficient as you won't need to DATE() anything.

 

The basic approach to the query is to start with the table you want data from (dates) then use a JOIN on another source (reservations) to filter out records you don't want.

SELECT d.date, d.time, r.count
FROM date table AS d
JOIN (
	SELECT date, COUNT(1) AS count
	FROM reservations
	GROUP BY date
	HAVING count < 80
) AS r ON d.date = r.date
ORDER BY d.date, d.time

The filtering (count