Fill In Missing Dates for SQL Server Query Output using CTE

Last week one of my colleagues requested me to help him write a query to fill in missing dates in query output. I came across couple of solutions, neither seemed convenient to me. So, I compiled my own using recursive CTE or Common Table Expression.

Problem Statement

Let’s say we have a table which contains incoming call records of a customer care from 1st to 10th June, 2021. In some days, there is no call record. If we run the GROUP BY statement on datetime column, some days will be missing. Desired output is, missing dates will be 0 value. Sample output will be below:

Query

SELECT CONVERT(varchar(10),B.call_time,111) AS OriginalDate, COUNT(*) as total
FROM Test1 B
GROUP BY CONVERT(varchar(10),B.call_time,111)
ORDER BY CONVERT(varchar(10),B.call_time,111)

Sample Output

Desired Output

 

My Approach to Solution

Rather than using simple GROUP BY query, CTE and SUB QUERY are used. Recursive CTE is used to generate the date range and LEFT OUTER JOIN is used to combine the value with the date. Let’s explain step-by-step.

CTE/Common Table Expression

CTE or Common Table Expression specifies a temporary named result set which is derived from a simple query and defined within the execution scope of a single SELECT/INSERT/UPDATE/DELETE/MERGE/CREATE VIEW statement. It can refer to itself also which is called recursive CTE.

Preparing Data

-- Create the table
CREATE TABLE Test1(
call_time datetime,
name    varchar(10) default ('Mehedi')
)
GO
-- Populate with sample data
INSERT INTO Test1 (call_time, name)
VALUES ('2021-06-01 08:00','A')
,('2021-06-01 09:05','C')
,('2021-06-01 12:50','E')
,('2021-06-01 16:17','D')
,('2021-06-01 18:53','G')
,('2021-06-03 11:07','F')
,('2021-06-03 13:09','A')
,('2021-06-03 16:26','E')
,('2021-06-03 19:56','C')
,('2021-06-03 21:24','A')
,('2021-06-04 19:13','A')
,('2021-06-04 11:45','B')
,('2021-06-04 15:02','C')
,('2021-06-08 23:02','A')
,('2021-06-09 03:04','E')

Build The Query

First, we will write a CTE which will generate all the dates within the date range.

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate = '2021-11-01'
SET @EndDate = '2021-11-08'
;WITH cte AS
(    SELECT @StartDate AS sDate
UNION ALL
SELECT DATEADD(DAY,1,sDate)
FROM cte
WHERE sDate < @EndDate
)
SELECT  sDate
FROM cte;

Now this CTE is will be refactored to make a sub query with LEFT OUTER JOIN so that the date which does not have the value appears and contains 0 value.

DECLARE @startdate DATETIME = '2021-06-01'
DECLARE @endDate DATETIME = '2021-06-10'
;WITH cte
AS
(
SELECT @startdate as sDate
UNION All
SELECT DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) <= @endDate
)
SELECT
C.OriginalDate
,C.total
FROM
(
SELECT CONVERT(varchar(10),A.sDate,111) AS OriginalDate, COUNT(B.call_time) as total
FROM cte A
LEFT OUTER JOIN Test1 B
ON A.sDate = CONVERT(varchar(10),B.call_time,111)
GROUP by CONVERT(varchar(10),A.sDate,111)
) C
ORDER BY C.OriginalDate

Final Output

Conclusion

Hope, it will be helpful for you. Happy TSQLing!

It’s also available in my personal blog!

 7,345 views last month,  2 views today

About M A A Mehedi Hasan

This is M A A Mehedi Hasan working in EBS Group since 2005. Starting career as a Software Developer, I am now Chief Technology Officer (CTO) of EBS Group. In this long journey, I have got opportunity to work with various technologies like Microsoft Data Platform, Telecom Value Added Service, and Audio/Video streaming.I have hands-on experience in managing Telecom Value Added projects, Web/App/Audio/Video streaming projects. Apart from these, I also manage large database, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow.Being a member of techforumbd (https://www.facebook.com/techforumbd), a Bangladeshi tech community, I regularly speak and organize sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. In free time, I love to play with Machine Learning. By the way, you can count me as a traveler too!Stay with my personal blog: www.techearth.xyz

Leave a Reply