I do have tours in a table which do have a start date and an end date, e.g.
id | start_date | end_date
1 2025-06-13 2025-06-13
2 2025-06-12 2025-06-17
3 2025-06-20 2025-06-21
4 2025-05-31 2026-06-02
and so on. What I need now is a count of tours for each day, even when there is no tour at this day. I have a query which has several issues:
SELECT start_date, COUNT(id) AS numberOfTours FROM tour WHERE start_date >= '2025-06-01' AND start_date =< '2025-06-30' GROUP BY start_date ORDER BY start_date;
The 3 I see issues are:
- only the start_date is taken into account. As a tour can last for more than one day, this is creating false counts
- only a row is created if there is a tour at this day. If a day does not have any tour, no row is created for this day.
- Tours that have their start_date prior to the date range are not taken into account (e.g. a tour which starts at 2025-05-31 but ends at 2025-06-02).
the perfect result would look like this:
date | numberOfTours
2025-06-01 1
2025-06-02 1
2025-06-03 0
2025-06-04 0
2025-06-05 0
2025-06-06 0
2025-06-07 0
2025-06-08 0
2025-06-09 0
2025-06-10 0
2025-06-11 0
2025-06-12 1
2025-06-13 2
2025-06-14 1
2025-06-15 1
2025-06-16 1
2025-06-17 1
2025-06-18 0
2025-06-19 0
2025-06-20 1
2025-06-21 1
2025-06-22 0
2025-06-23 0
2025-06-24 0
2025-06-25 0
2025-06-26 0
2025-06-27 0
2025-06-28 0
2025-06-29 0
2025-06-30 0
If it is any help: I do have a day table where each day is represented by a single row.
Thanks for your help & best regards
2
SELECT date,
(SELECT COUNT(id)
FROM tour
WHERE tour.start_date <= day.date
AND tour.end_date >= day.date
) AS numberOfTours
FROM day
WHERE date > '2025-07-01'
AND date < '2025-07-31'
ORDER BY date;
quite easy 🙂