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.
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:
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)
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.
-- Create the table
CREATE TABLE Test1(
name varchar(10) default ('Mehedi')
-- Populate with sample data
INSERT INTO Test1 (call_time, name)
VALUES ('2021-06-01 08:00','A')
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
WHERE sDate < @EndDate
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'
SELECT @startdate as sDate
SELECT DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) <= @endDate
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)
ORDER BY C.OriginalDate
Hope, it will be helpful for you. Happy TSQLing!
It’s also available in my personal blog!
8,001 views last month, 7 views today