How to get one row per date using start and end dates (SQL)?

Question

I'm new to SQL and was wondering if someone can help me come up with a query.

I need to go from this:

To this:

Basically, I have a table with some class labels and their start and end dates. I would like to have one row per date between the start and end dates. I have tried looking up some date functions in SQL and know I probably have to use DATEADD at some point, to add days to the start date until we reach the end date. Beyond this I am pretty stuck and would appreciate any help with this! Thanks

Please post the samples as text, not images. For reasons why, see https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-errors-when-asking-a-question . Also, please show us what you've tried so far and the result. (We understand whatever you have isn't working, but it will demonstrate effort and provide a starting point.) — SOS, Mar 20 '22 at 01:16
Do you use Postgres of MySQL? Solutions differ. For Postgres: https://stackoverflow.com/a/46499873/939860 — Erwin Brandstetter, Mar 20 '22 at 01:48
Does this answer your question? [Generating time series between two dates in PostgreSQL](https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql) — SOS, Mar 20 '22 at 03:11

score 0 · Answer 1 · answered Mar 20 '22 at 01:50

I would use a recursive cte to build up the date sequence and join it to your class table. Something like -

with recursive date_range (date) AS (
    select min(start_date) from t
    union all
    select date(date + interval '1day') from date_range where date + interval '1day' <= (select max(end_date) from t)
)
select t.*, dr.*
from date_range dr
join t
    on dr.date between t.start_date and t.end_date
order by t.class, dr.date

Here's a db<>fiddle

How to get one row per date using start and end dates (SQL)?

1 Answers1