Expanding Date Ranges

I want to expand the date ranges in the below “Original Table”. The end result should be the “Resultant Table”. I know that this can be done in sql using dateadd and ctes. However, I cannot get the join right to get the result as shown below. Please suggest possible solutions.

The query I am using is something like this:

WITH Calendar AS  
(  
  select DATEADD(day, 1, '2019-08-01') as date
  UNION ALL  
  select DATEADD(day, 1, date) 
  from Calendar 
  where date <= '2019-08-05'  
)
SELECT o.Column1,o.Column2,o.Column3, calendar.date
FROM OriginalTable o
 inner join calendar
on calendar.date=o.Column3

The result it gives me is the original table. I have played around with various options and it gives me other incorrect results.

Answer

The immediate problem with your query is that you should be using a left join rather than an inner join in the outer query, so “missing” dates are not filtered out.

However, this is probably not sufficient. You also need to keep track of the original values so you can put them in the newly generated rows.

It might be simpler to generate the rows directly from the recursive query:

with cte as (
    select column1, column2, column3, lead(column3) over(order by column3) lead_column3
    from original table
    union all
    select column1, column2, dateadd(day, 1, column3), lead_column3
    from cte
    where dateadd(day, 1, column3) < lead_column3
)
select * from cte