SQL: Getting Missing Date Values and Copy Data to Those New Dates

So this seems somewhat weird, but this use case came up, and I have been somewhat struggling trying to figure out how to come about a solution. Let’s say I have this data set:

date	value1	value2
2020-01-01	50	2
2020-01-04	23	5
2020-01-07	14	8

My goal is to try and fill in the gap between the two dates while copying whatever values were from the date before it. So for example, the data output I would want is:

date	value1	value2
2020-01-01	50	2
2020-01-02	50	2
2020-01-03	50	2
2020-01-04	23	5
2020-01-05	23	5
2020-01-06	23	5
2020-01-07	14	8

Not sure if this is something I can do with SQL but would definitely take any suggestions.

Answer

One approach is to use the window function lead() in concert with an ad-hoc tally table if you don’t have a calendar table (highly suggested).

Example

;with cte as ( 
Select * 
      ,nrows = datediff(day,[date],lead([date],1,[date]) over (order by [date]))
 From YourTable A
)
Select date = dateadd(day,coalesce(N-1,0),[date])
      ,value1
      ,value2
 From  cte A
 left Join (Select Top 1000 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1 ) B
   on N<=nRows

Results

date        value1  value2
2020-01-01  50      2
2020-01-02  50      2
2020-01-03  50      2
2020-01-04  23      5
2020-01-05  23      5
2020-01-06  23      5
2020-01-07  14      8

EDIT: If you have a calendar table

Select Date = coalesce(B.Date,A.Date)
      ,value1
      ,value2
 From (
        Select Date
              ,value1
              ,value2
              ,Date2 = lead([date],1,[date]) over (order by [date])     
         From  YourTable A
      ) A
  left Join  CalendarTable B on B.Date >=A.Date and B.Date< A.Date2