Skip to content
Advertisement

SQL Generate per day records between dates

I have a table that where each record has start and end dates. I need to return a record for each day between start and end date fields (including the start and end dates). Using MS SQL Server.

Example:

Current data

enter image description here

Data required:

enter image description here

Looking for recommendations. Thanks.

Advertisement

Answer

You can use recursive cte :

with cte as (
     select id, startdate, enddate, startdate as date
     from table t
     union all
     select id, startdate, enddate, dateadd(day, 1, date)
     from cte c
     where date < enddate
)
select *
from cte c
option (maxrecursion 0);
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement