Select row for each date in range

Question

I have a table which has personIds, a start date and an end date.

I need to select each personId-date pair, but the dates are in a range specified by two columns.

For example:

personId | startDate | endDate
1 | 2018-05-10 | 2018-05-13

would return

personId | date
1 | 2018-05-10
1 | 2018-05-11
1 | 2018-05-12
1 | 2018-05-13

score 0 · Answer 1 · answered May 13 '18 at 14:01

0

One option here would be to create a calendar table containing all the dates, inclusive, for 2018 (and possibly other years as well). Then, just inner join this calendar table to the record for a given person.

SELECT c.date
FROM calendar c
INNER JOIN yourTable t
    ON c.date BETWEEN t.startDate AND t.endDate;

See here for some good options on creating a calendar table in MySQL.

answered May 13 '18 at 14:01

Tim Biegeleisen

502,043
27
286
360

Anyway to avoid using a calendar table? My database has dates going back many years. – Sam596 May 13 '18 at 15:45
Yes, you might be able to handle it using dynamic SQL. But even then you would be generating the data, I think. – Tim Biegeleisen May 13 '18 at 15:49

Select row for each date in range

1 Answers1