Alejandro Landeros Alejandro Landeros - 1 year ago 122
MySQL Question

How to retrieve count from date ranges that overlap?

I want to get the count of records that are within a x day interval. The intervals are overlapping though. Example: For a given time frame (lets say 30 days) I want to get the count for the first 5 days ( day 1 - day 5 inclusive), then count for day 2 - day 6, then day 3 - day 7, then day 4 - day 8, etc etc until I reach day 26 - day 30.

Whats the best way to go about this?
Is it possible to retrieve all the data for 30 days then group by?
Get queries for each sub interval then union them together?
Another method?
What would the query look like?

Example output, range is not necessarily but helpful.

count, range
30, 2016-01-01 - 2016-01-05
32, 2016-01-02 - 2016-01-06
34, 2016-01-03 - 2016-01-07
33, 2016-01-04 - 2016-01-08

Hopefully its understandable, if not please let me know and i'll clarify.


Answer Source

You need to join with a table that contains each date range. You can create this on the fly with a bunch of UNIONs.

SELECT t1.start, t1.end, COUNT( AS count
FROM (SELECT '2016-01-01' AS start, '2016-01-05' AS end
      SELECT '2016-01-02', '2016-01-06'
      SELECT '2016-01-03', '2016-01-07'
      SELECT '2016-01-04', '2016-01-08'
      ...) AS t1
LEFT JOIN yourTable AS t2 ON BETWEEN t1.start AND t1.end
GROUP BY t1.start

The LEFT JOIN ensures that you see the 0 counts for any date ranges with no records.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download