CODEWITHSUNDEEP
mysqlsql
user1090729
user1090729

Reputation: 1563

SQL Get a result comparing aggregate functions

I have a table that looks like this:

code int, primary key
reservation_code int,
indate date,
outdate date,
slot int,
num int,

The database has a somewhat weird design to it, the way it is supposed to work is that this table keeps the dates of every slot booking, num is used to keep track of contiguous bookings for what I believe are legacy reasons.

I'm trying to come up with a query that checks for prior bookings in the database. My idea of doing this:

For a given slot number, check if there is a group of rows with the same reservation_code which on the row with the minimum num value of the group has an indate date that is lower or equal than a given date and the outdate date on the row with the max num value is higher than the same given date.

My closest approach to this in SQL:

EDIT: With help from Barmar

SELECT b.reservation_code
FROM bookings b
JOIN (SELECT reservation_code, MIN(num) minnum
      FROM bookings
      WHERE slot = "given_slot"
      AND indate <= "given_date"
      GROUP BY reservation_code) min
ON minnum = num and b.reservation_code = min.reservation_code
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given_slot"
      AND outdate > "given_date"
      GROUP BY reservation_code) max
ON maxnum = num and b.reservation_code = max.reservation_code
WHERE slot="given_slot"
AND indate <= "given_date"
AND outdate > "given_date"
GROUP BY b.reservation_code

Adding GROUP BY to both subqueries makes it work for most cases, but the second check still returns a wrong answer.

Here are some sample rows and queries to try to make the question a bit clearer:

sample rows:

code    reservation_code    indate      outdate     slot    num
1       1                   01/01/13    03/01/13    1       0
2       1                   03/01/13    05/01/13    1       1
3       1                   05/01/13    10/01/13    1       2
4       2                   04/01/13    15/01/13    2       0
5       2                   15/01/13    19/01/13    2       1
6       3                   11/01/13    13/01/13    1       0
7       4                   15/01/13    16/01/13    3       0
8       5                   01/01/13    15/01/13    3       0
9       5                   15/01/13    25/01/13    4       1

Sample checks:

slot 2, date 21/02/13, should return not booked.
slot 2, date 16/01/13, should return booked
slot 1, date 14/01/13, should return not booked
slot 1, date 12/01/13, should return booked
slot 1, date 10/01/13, should return not booked
slot 3, date 02/01/13, should return booked
slot 4, date 15/01/13, should return booked
slot 4, date 25/01/13, should return not booked

Upvotes: 0

Views: 2566

Answers (2)

user1090729
user1090729

Reputation: 1563

After a good night's sleep I realized my problem was rather trivial and could be solved with a very simple query such as:

SELECT 1
FROM bookings
WHERE slot="given slot"
AND indate <= "given date"
AND outdate > "given date"

I want to thank everyone who tried to help me, I'm sorry I wasted your time with this.

Upvotes: 0

Barmar
Barmar

Reputation: 781716

You need to use a JOIN with the aggregated table

SELECT b.reservation_code, count(1)
FROM bookings b
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given slot"
      AND indate <= "given date"
      GROUP BY reservation_code) m
ON maxnum = num and b.reservation_code = m.reservation_code
WHERE slot="given slot"
AND indate <= "given date"
GROUP BY b.reservation_code

Upvotes: 2

Related Questions