MySQL count(*) each day for next 7 days

Question

I have the following query which counts the number of bookings for a selected day

 select count(*) 
 from  isBooked inner join booking
 on isbooked.BookingID = booking.bookingID
 where '2015-08-09' between booking.startDate and booking.endDate;

I want to run this query for the next 7 days and display the count for each day, for example

day     count
1        10
2        9
3        18
4        6
5        1
6        9
7        14

Answer 1

Basically it can be an UNION of 7 queries :

(
    SELECT CURDATE() AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE CURDATE() BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 1 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 2 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 3 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 4 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 5 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 6 DAY) BETWEEN booking.startDate and booking.endDate;
)

But if you have a large amount of data to manage, you shoud consider processing it regularily on your database and putting it in some cache file or other table.

If you want to do this for a lot of days, an UNION of N SELECT's will not be efficient. In this case I would recommand defining a (temporary) table containing the days, and doing a sigle query with a JOIN on the dates, for example :

CREATE TEMPORARY TABLE dates (day DATE); -- not necessarily temporary

INSERT INTO dates (day) values ('2015-01-01'), ....

SELECT dates.day AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE dates.day BETWEEN booking.startDate and booking.endDate
        GROUP BY dates.day;