SQL Adding extra rows until dates (YYYYMMDD) columns match

Table1:

ID|StartDateID|EndDateID 
468|20200101|20200104
534|20200103|20200104
123|20200106|20200108

Required output:

ID|StartDateID|EndDateID 
468|20200101|20200104
468|20200102|20200104
468|20200103|20200104
468|20200104|20200104
534|20200103|20200104
534|20200104|20200104
123|20200106|20200108
123|20200107|20200108
123|20200108|20200108

Apologies if this is poorly explained. Table1 lists the start and end dates of a task completion. Each ID is an unique entry.

I need an extra row for each StartDateID (adding 1 each time), until it matches EndDate.

A day is added to StartDateID until it matches EndDateID, at which point we stop replication.

Does that make sense?

I’ve played around with CTE, but got nowhere.

Answer

Use a recursive CTE. Assuming that the columns are actually dates:

with cte as (
      select ID, StartDateID, EndDateID 
      from t
      union all
      select id, dateadd(day, 1, startdateid), enddateid
      from cte
      where startdateid < enddateid
     )
select *
from cte;

If the columns are not dates, I would advise converting them:

with cte as (
      select ID, convert(date, StartDateID) as startdate, convert(date, EndDateID) as enddate
      from t
      union all
      select id, dateadd(day, 1, startdate), enddate
      from cte
      where startdate < enddate
     )
select *
from cte;

Here is a db<>fiddle.

If your spans can exceed 100 days, then you need to add OPTION (MAXRECURSION 0) to the query.