mysql - count how many event between dates sql in different rows - Stack Overflow

admin管理员组

文章数量:1277896

I am trying to count number of overlapping events between dates in SQL. Let’s assume some dates for room reservations in a hotel. Like this:

start	end
14.01.2024	17.01.2024
15.01.2024	17.01.2024
17.01.2024	19.01.2024

I am trying to count number of overlapping events between dates in SQL. Let’s assume some dates for room reservations in a hotel. Like this:

start	end
14.01.2024	17.01.2024
15.01.2024	17.01.2024
17.01.2024	19.01.2024

I need to find how many rooms are booked every day (the checkout date is included). In my case it should be:

date	No of rooms
14.01.2024	1
15.01.2024	2
16.01.2024	2
17.01.2024	3
18.01.2024	1
19.01.2024	1

I can generate time series of a given range (14.01 – 19.01 in this case) and join it to the data, but I am allowed to work only with a singe table. So, what I have to work with is:

time_series	start	end
14.01.2024	14.01.2024	17.01.2024
15.01.2024	15.01.2024	17.01.2024
16.01.2024	null	null
17.01.2024	17.01.2024	19.01.2024
18.01.2024	null	null
19.01.2024	null	null

I am looking for a select query that will take every value of the time_series column and count rows which have this date between start and end (end is included). Is there any way to do this with a sort of common table expression?

• mysql

Share Improve this question Follow asked Feb 24 at 20:53 AstAAstA 1111 bronze badge 4

• 1 "I am allowed to work only with a singe table" don't know what you mean by that, or what that has to do with joining a given range. do you mean you aren't allowed to join? would a join in a cte be ok? – ysth Commented Feb 24 at 20:59
• You could use one of these options to generate result set with a range of whatever dates you like to drive this. It would just be a join between this derived result set (in a CTE or subquery) and your table. – JNevill Commented Feb 24 at 20:59
• Join the time series with the reservation table, using BETWEEN start AND end in the ON condition. – Barmar Commented Feb 24 at 21:04
• 1 "what I have to work with is" when there are multiple reservations starting the same day, what does that look line in your table, and what you do want the results to look like? – ysth Commented Feb 24 at 21:05

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

It's not entirely clear what your "use only one table" restriction means.

Let's say you somehow got this table date_series:

time_series	start_date	end_date
2024-01-14	2024-01-14	2024-01-17
2024-01-15	2024-01-15	2024-01-17
2024-01-16	null	null
2024-01-17	2024-01-17	2024-01-19
2024-01-18	null	null
2024-01-19	null	null

Using this table, count by self JOIN date_series.
We take DISTINCT time_series for case where several reservations have same date.

select  t1.time_series,count(t2.start_date) as No_of_rooms
from (select distinct time_series from date_series) as t1
left join date_series t2 on t1.time_series between t2.start_date and t2.end_date
group by t1.time_series

time_series	No_of_rooms
2024-01-14	1
2024-01-15	2
2024-01-16	2
2024-01-17	3
2024-01-18	1
2024-01-19	1

fiddle

A more common way to solve such a problem

1. Generate date_range. In this example by recursive query. More examples see reference in @JNevill comment.
2. Join source table with date_range, then count(start_date) thru GROUP BY.

See exmple

with recursive date_range as(
  select cast('2024.01.14' as date) as report_date, cast('2024.01.19' as date) to_date
  union all
  select date_add(report_date,interval 1 day), to_date
  from date_range
  where report_date<to_date
)
select report_date,count(start_date) as No_of_rooms
from date_range d
left join reservations r on d.report_date between r.start_date and r.end_date
group by report_date

report_date	No_of_rooms
2024-01-14	1
2024-01-15	2
2024-01-16	2
2024-01-17	3
2024-01-18	1
2024-01-19	1

fiddle

Your intermediate table somewhat is the dump of what you would have got from a CTE.

Joining the table to itself, by considering only the first column in its first (left) use, and only the last two columns on the right side, will emulate the two original tables.

select t.time_series, count(1) no_of_rooms
from ts t join ts f on t.time_series between f.start and f.end
group by t.time_series
order by t.time_series;

See it in a fiddle.

Note that the comparison to the null-valued columns of the right side will naturally discard rows for that second table when the row has only a first table use (e.g. the 16.01 which is not a booking occurrence but only a series date).

Handling of same-date bookings

Note that this solution entirely relies on the careful building of your intermediate table.
In particular when two bookings start as the same date only one should get a valued time_series, the other one shall have a null in that column.

See a fiddle with another booking to illustrate that.

IMHO

That "only one table" requirement looks suspicious, because it complicates everything and essentially forces to recompose (deduce) the original table + the series from your intermediate table.

On the contrary, the requirement may be a way to force you to not create another, intermediate table, to direct you to a full-CTE solution (including the series generation):
instead of create table series_and_bookings as select …; select [final query]; it would like to incite you to write with series_and_bookings as (select …) select [final query];.
Really we can say that it doesn't add a table (in case of a crash a CTE won't let artifacts, which I think is the real requirement).

Thus you may want to challenge the requirement, at least ask for a clarification.

本文标签： mysqlcount how many event between dates sql in different rowsStack Overflow