Get missing dates for a given ID and populate data for those in Sql server

Question

I need to populate data for the missing dates in a table for a given ID. Populate the data for those dates. please see below sample data.

Could you please help with the script, i need to apply this for a large dataset in a USP.

Data sample :

CREATE #TBL

(

DATE1 DATETIME

,ID INT

,Col1 Varchar(5)

,Col2 Varchar(5)

)

Insert into #TBL

(’12/1/22’, 1001, ‘abc’, ‘df’)

,(’12/5/22’, 1001, ‘abc’, ’def’)

,(’12/5/22’, 1002, ‘dcb’, ’ef’)

,(’12/10/22’, 1003, ‘cdf’, ’efg’)

,(’12/10/22’, 1001, ‘abc’, ‘efg’)

Answer 1

Try this query SQL Server 2019 and previous versions:

;
with Q as
(
	select DATE1, ID, Col1, Col2, 
		datediff(day, DATE1, LEAD(DATE1) over (partition by ID order by DATE1)) - 1 as g
	from #TBL
	union all
	select DATE1, ID, Col1, Col2, g - 1
	from Q
	where g - 1 >= 0
)
select dateadd(day, coalesce(g, 0), DATE1) as DATE1, ID, Col1, Col2
from Q
order by ID, DATE1
option (maxrecursion 0)

By the way, the sample input data seem incomplete.

Answer 2

Hi @Suman Gupta

Try this query:

DECLARE @Max_Date DATETIME 
SELECT @Max_Date = MAX(DATE1) FROM #TBL

;WITH CTE1 AS
(
 SELECT DATE1 AS StartDay,
        ISNULL(DATEADD(DAY,-1,LEAD(DATE1)OVER(PARTITION BY ID ORDER BY DATE1)),@Max_Date) AS EndDate,
		ROW_NUMBER()OVER(PARTITION BY ID ORDER BY DATE1) AS RNum,
	    ID,Col1,Col2 
 FROM #TBL
),CTE2 AS
(
 SELECT StartDay,EndDate,ID,Col1,Col2,RNum
 FROM CTE1
 UNION ALL
 SELECT DATEADD(DAY,1,C2.StartDay),C1.EndDate,C1.ID,C1.Col1,C1.Col2,C1.RNum
 FROM CTE2 C2 JOIN CTE1 C1 ON C2.ID=C1.ID AND C2.RNum=C1.RNum AND DATEADD(DAY,1,C2.StartDay) <= C1.EndDate
)
SELECT StartDay AS [Date],ID,Col1,Col2 
FROM CTE2
ORDER BY ID,StartDay

Output:

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Try this query in SQL Server 2022:

;
with Q as
(
	select *, 
		datediff(day, DATE1, LEAD(DATE1) over (partition by ID order by DATE1)) as g
	from #TBL
)
select dateadd(day, s.value, DATE1) as Date, ID, Col1, Col2
from Q
cross apply generate_series(0, coalesce(g - 1, 0)) s
order by ID

By the way, the sample input data seem incomplete.

Answer 4

Another solution:

CREATE table #TBL
(DATE1 DATETIME
,ID INT
,Col1 Varchar(5)
,Col2 Varchar(5)
)
Insert into #TBL values
('12/1/22', 1001, 'abc', 'df')
,('12/5/22', 1001, 'abc', 'def')
,('12/5/22', 1002, 'dcb', 'ef')
,('12/10/22', 1003, 'cdf', 'efg')
,('12/10/22', 1001, 'abc', 'efg')
declare @startdate date
declare @enddate date  
 
Select @startdate = min(DATE1),@enddate = max(DATE1) from #TBL 
 
--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)
 
,mycte as
(
select  DATE1,ID,Col1,COl2, dateadd(day,n-1,@startdate) dt ,DATE2
from Nums 
Cross apply (Select Distinct DATE1,ID,Col1,COl2 ,LEAD(DATE1) over (partition by ID,Col1 order by DATE1 )  DATE2 From  #TBL ) a   
WHERE dateadd(day,n-1,@startdate)<=@enddate 
)
,mycte2 as (
Select DATE1, dt, c.ID,c.Col1,c.Col2  ,DATE2
FROM mycte c 
WHERE c.dt<=@enddate --last date
 )
 select dt,ID,Col1,Col2
 
 from mycte2  
 where dt>=DATE1 and (dt<DATE2 or DATE2 is null)
 Order by 2,1
 
 
drop table  #TBL