Below is a subset of my table (for the first id)

For each id, the dates are not consecutive (e.g., for id 1, the min date is 01/01/2022 and the max date is 08/01/2022)--there are 7 days in between both dates. I want to insert rows to make the dates for each id consecutive and contiguous - the value for the value field/column to be filled with 0s so that the updated table looks like:

Any SQL code on how to implement this would be highly appreciated. I have a calendar table but am unsure how to join it with the above table so that I fill in missing dates dynamically for each id with 0s.

My calendar table looks like:

CodePudding user response：

Considering you state you have a calendar table, it seems what you need to do with JOIN to it with the MIN and MAX dates from your other table, and the LEFT JOIN back to your table:

WITH MinMax AS(
    SELECT ID,
           MIN(date) AS MinDate,
           MAX(date) AS MaxDate
    FROM dbo.YourTable
    GROUP BY ID),
Dates AS(
    SELECT MM.ID,
           C.CalendarDate AS [Date]
    FROM MinMax MM
         JOIN dbo.CalendarTable C ON MM.MinDate <= C.CalendarDate
                                 AND MM.MaxDate >= C.CalendarDate)
SELECT D.ID,
       D.[Date],
       ISNULL(YT.[Value],0) AS [Value]
FROM Dates D
     LEFT JOIN dbo.YourTable YT ON D.ID = YT.ID
                               AND D.[Date] = YT.[Date];

CodePudding user response：

SET DATEFORMAT DMY

-- CREATE A TABLE WITH OUR INPUT DATA
DROP TABLE IF EXISTS #TheData
GO
CREATE TABLE #TheData
(TheDate DATE, id INT, TheValue INT)
INSERT INTO #TheData
(TheDate,id,Thevalue)
VALUES
('01/01/2022',1,5),
('08/01/2022',1,2),
('17/01/2022',2,7),
('25/01/2022',2,7),
('15/02/2022',2,7)

-- CREATE A CALENDAR CTE

DECLARE @StartDate  date = '20210101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 2, @StartDate));

;WITH DateSeq(TheDate) AS 
(
  SELECT @StartDate 
  UNION ALL 
  SELECT DATEADD(dd,1,TheDate) FROM DateSeq
  WHERE TheDate < @CutoffDate
)

-- CROSS JOIN OUR CALENDAR CTE TO OUR SOURCE DATA. DERIVED TABLE TO GET FIRST AND LAST OF EACH RANGE TO USE FOR JOIN
SELECT
ds.*
,SourceDataRangesByID.ID
,ISNULL(td.TheValue,0) AS TheValue
FROM
DateSeq ds
CROSS JOIN
(
    SELECT 
    d.ID
    ,MIN(d.TheDate) AS MinDatePerID
    ,MAX(d.TheDate) AS MaxDatePerID
    FROM #TheData d 
    GROUP BY d.ID
) SourceDataRangesByID 
LEFT JOIN #TheData td ON td.id = SourceDataRangesByID.ID AND td.TheDate = ds.TheDate
WHERE ds.TheDate >= SourceDataRangesByID.MinDatePerID
AND ds.TheDate <= SourceDataRangesByID.MaxDatePerID

OPTION (MAXRECURSION 0);

CodePudding user response：

try the generate_series to create a date table then right join with it and coalesce for the non null value

SELECT generate_series('2016-01-01',  -- series start date
                           '2018-06-30',  -- series end date
                           '1 day'::interval)::date AS day) AS daily_series
   from mytable

See Generate_Series for TSQL

 https://dba.stackexchange.com/questions/255165/does-ms-sql-server-have-generate-series-function 

(Sql server 2022)

https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16