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

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.

CodePudding user response：

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

CodePudding user response：

Another option is to use CROSS APPLY. I am not sure how you are determining what range you want from the table, but you can easily override my guess by explicitly defining @s and @e:

DECLARE @s date, @e date;

SELECT @s = MIN(date), @e = MAX(date) FROM dbo.TheTable;

;WITH d(d) AS
(
  SELECT @s UNION ALL 
  SELECT DATEADD(DAY,1,d) FROM d
  WHERE d < @e
)
SELECT d.d, x.value1, x.value2
FROM d CROSS APPLY
(
  SELECT TOP (1) value1, value2
  FROM dbo.TheTable 
    WHERE date <= d.d
    AND value1 IS NOT NULL
  ORDER BY date DESC
) AS x
-- OPTION (MAXRECURSION 32767) -- if date range can be > 100 days but < 89 years
-- OPTION (MAXRECURSION 0)     -- if date range can be > 89 years

If you don't like the recursive CTE, you could easily use a calendar table (but presumably you'd still need a way to define the overall date range you're after as opposed to all of time).

Example db<>fiddle

CodePudding user response：

In SQL Server you can make a cursor, which iterates over the dates. If it finds values for a given date, it takes those and stores them for later. in the next iteration it can then take the stored values, in case there are no values in the database