Search code examples
sqldaterecursionmissing-data

How to repeat dates for multiples values and fill missing dates in SQL

I have a table with date, ID and Values, which looks like this

enter image description here

I want to repeat the date range(start date, end date) for all IDs, and if the value is missing for any date enter 0.

enter image description here

How can I achieve this in SQL? Thanks in Advance

Solution

  • I have tried below example in SQL Server.

    1. First you need to have a date table with the list of dates.
    2. Then, you need to have the combination of date, id values
    3. Now, you need to fill the val if it exists for the date, otherwise, it will be 0.
    DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '2020-02-01';
SET @ToDate = '2020-02-28';
DECLARE @DateTable TABLE(theDate DATE) 
DECLARE @datasource TABLE(id int, dateVal DATE, Val INT)

-- all days in that period
INSERT INTO @DateTable 
SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate)+1) 
  TheDate = CAST(DATEADD(DAY, number, @FromDate) AS DATE)
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;

--
INSERT INTO @datasource
VALUES
(59895,'2020-02-01',5),(59895,'2020-02-03',7),
(59888,'2020-02-01',2),(59888,'2020-02-02',10)

;WITH CTE_idDates AS
(
SELECT theDate,id FROM @DateTable
CROSS JOIN 
@datasource
)
SELECT theDate, id,
COALESCE((SELECT Val from @dataSource WHERE dateval=c.thedate and id=c.id),0)
FROM CTE_idDates AS c

    Result set

    +------------+-------+------------------+
|  theDate   |  id   | (No column name) |
+------------+-------+------------------+
| 2020-02-01 | 59895 |                5 |
| 2020-02-02 | 59895 |                0 |
| 2020-02-03 | 59895 |                7 |
| 2020-02-04 | 59895 |                0 |
| 2020-02-05 | 59895 |                0 |
| 2020-02-06 | 59895 |                0 |
.
.
.
| 2020-02-28 | 59895 |                0 |
| 2020-02-01 | 59888 |                2 |
| 2020-02-02 | 59888 |               10 |
.
.
.
| 2020-02-26 | 59888 |                0 |
| 2020-02-27 | 59888 |                0 |
| 2020-02-28 | 59888 |                0 |
+------------+-------+------------------+