How to insert values based on another column value

Question

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

date	id	value
01/01/2022	1	5
08/01/2022	1	2

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:

date	id	value
01/01/2022	1	5
02/01/2022	1	0
03/01/2022	1	0
04/01/2022	1	0
05/01/2022	1	0
06/01/2022	1	0
07/01/2022	1	0
08/01/2022	1	2

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:

date
01/01/2022
02/01/2022
03/01/2022
04/01/2022

Thank you provided that I have a Calendar table, please could you provide the code to implement my requirement? Thanks @Larnu — j5934
– j5934, Commented Oct 18, 2022 at 14:08
@Larnu I have a calendar table but am unsure how to join in with the above table so that I fill in missing dates dynamically for each id with 0s based on the min and max date for each id. Many thanks. — j5934
– j5934, Commented Oct 18, 2022 at 14:09
If you already have a calendar table, share what you've tried and add both the table and your attempt to this post. — lemon
– lemon, Commented Oct 18, 2022 at 14:14
@lemon i have added in the Calendar in my recent edit. As for my attempt, I am unsure as to where to start since my table has many ids with differing min and max dates. — j5934
– j5934, Commented Oct 18, 2022 at 14:17

Thom A · Accepted Answer · 2022-10-18 14:24:52Z

1

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];

answered Oct 18, 2022 at 14:24

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

planetmatt · Accepted Answer · 2022-10-18 14:59:18Z

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

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-10-18 20:16:06Z

0

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

answered Oct 18, 2022 at 20:16

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

How to insert values based on another column value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related