CODEWITHSUNDEEP
sqlsql-server
MrTNader
MrTNader

Reputation: 37

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.

Upvotes: 0

Views: 725

Answers (3)

anon
anon

Reputation:

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).

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 5

Michza
Michza

Reputation: 39

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

Upvotes: -1

Related Questions