Question 253592 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Selecting dates of month based on existing rows

I have a table with the following schema:

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bookingDate` date NOT NULL,
 PRIMARY KEY(`id`)

I need to get all dates of the current month where there’s less than 5 bookings. (meaning where less than 5 rows exist where bookingDate == date)

I tried iterating over every day of the month individually, but for performance reasons it really doesn’t make sense to perform +/- 30 queries for every month.

Tags:

3

Answers


  1. 0

    Hope this helps:

    SELECT id, COUNT(bookingDate)
FROM table_name
GROUP BY id
HAVING COUNT(bookingDate) < 5;
    Login or Signup to reply.
  2. 0

    There are many different ways of tackling this.

    This is not one of them:

    SELECT bookingDate
FROM bookings
WHERE bookingDate BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY bookingDate
HAVING COUNT(*) < 5;

    This will only return bookingDates with 1, 2, 3 or 4 bookings, as it does not know about dates with zero bookings.

    You could invert the above query and iterate over the result in your client application, unsetting all dates that exist in the result:

    SELECT bookingDate
FROM bookings
GROUP BY bookingDate
HAVING COUNT(*) >= 5;

    But, that just feels clunky to me.

    You could start with the full list of days you are interested in, generated using MariaDB’s Sequence Storage Engine:

    /* Full list of days in September 2023 based on first
 * of month and number of days in month */
SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_30;

/* Full list of days in current month */
SELECT CURRENT_DATE - INTERVAL (DAY(CURRENT_DATE) - 1) DAY + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_31
WHERE seq <= DAY(LAST_DAY(CURRENT_DATE));

    Obviously, there is no need for this step if you already have a calendar table with all dates, or a contiguous set to work with.

    And then LEFT JOIN to your bookings:

    SELECT s.dt, COUNT(b.id) AS num_bookings
FROM (
    SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
    FROM seq_1_to_30
) s
LEFT JOIN bookings b
    ON s.dt = b.bookingDate
GROUP BY s.dt
HAVING num_bookings < 5;

    Here’s a db<>fiddle to play with.

    Note: Your current table is lacking an index on bookingDate:

    ALTER TABLE bookings ADD INDEX idx_booking_date (bookingDate);
    Login or Signup to reply.
  3. 0

    To achieve this more efficiently, you can use a single query. Here’s a step-by-step breakdown of the solution:

    Generate all dates for the current month.
    LEFT JOIN the generated dates with the booking table on the bookingDate.
    Group by the date and count the bookings for each date.
    Filter out the dates that have 5 or more bookings.
    Here’s how you can do it:

    -- Step 1: Generate all dates for the current month
WITH RECURSIVE DateSequence AS (
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS dateValue -- Start from the first day of the current month
    UNION ALL
    SELECT DATE_ADD(dateValue, INTERVAL 1 DAY)
    FROM DateSequence
    WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= LAST_DAY(CURDATE()) -- Up to the last day of the current month
)

-- Step 2, 3, and 4
SELECT ds.dateValue
FROM DateSequence ds
LEFT JOIN YourTableName t ON ds.dateValue = t.bookingDate
GROUP BY ds.dateValue
HAVING COUNT(t.id) < 5;

    Replace YourTableName with the name of your booking table. The result of this query will give you all the dates of the current month where there are less than 5 bookings.

    The advantage of this approach is that you get your result in one query, reducing the overhead of multiple individual queries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search