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?
What would the query look like?
Example output, range is not necessarily but helpful.
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
You need to join with a table that contains each date range. You can create this on the fly with a bunch of
SELECT t1.start, t1.end, COUNT(t2.date) AS count FROM (SELECT '2016-01-01' AS start, '2016-01-05' AS end UNION SELECT '2016-01-02', '2016-01-06' UNION SELECT '2016-01-03', '2016-01-07' UNION SELECT '2016-01-04', '2016-01-08' ...) AS t1 LEFT JOIN yourTable AS t2 ON t2.date BETWEEN t1.start AND t1.end GROUP BY t1.start
LEFT JOIN ensures that you see the 0 counts for any date ranges with no records.