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:<\/p>\n
Query<\/span><\/p>\n Sample Output<\/span><\/p>\n Desired Output<\/span><\/p>\n <\/p>\n My Approach to Solution<\/strong><\/span><\/p>\n 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.<\/p>\n CTE\/Common Table Expression<\/strong><\/span><\/p>\n 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.<\/p>\n Preparing Data<\/strong><\/span><\/p>\n Build The Query<\/strong><\/span><\/p>\n First, we will write a CTE which will generate all the dates within the date range.<\/p>\n 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.<\/p>\nSELECT CONVERT(varchar(10),B.call_time,111) AS OriginalDate, COUNT(*) as total
\nFROM Test1 B
\nGROUP BY CONVERT(varchar(10),B.call_time,111)
\nORDER BY CONVERT(varchar(10),B.call_time,111)<\/code><\/p>\n![\"\"](\"https:\/\/ittutorial.org\/wp-content\/uploads\/2021\/06\/missing_date_1-300x108.png\")
<\/p>\n![\"\"](\"https:\/\/ittutorial.org\/wp-content\/uploads\/2021\/06\/missing_date_2-300x243.png\")
<\/p>\n-- Create the table
\nCREATE TABLE Test1(
\ncall_time datetime,
\nname\u00a0\u00a0\u00a0 varchar(10) default ('Mehedi')
\n)
\nGO
\n-- Populate with sample data
\nINSERT INTO Test1 (call_time, name)
\nVALUES ('2021-06-01 08:00','A')
\n,('2021-06-01 09:05','C')
\n,('2021-06-01 12:50','E')
\n,('2021-06-01 16:17','D')
\n,('2021-06-01 18:53','G')
\n,('2021-06-03 11:07','F')
\n,('2021-06-03 13:09','A')
\n,('2021-06-03 16:26','E')
\n,('2021-06-03 19:56','C')
\n,('2021-06-03 21:24','A')
\n,('2021-06-04 19:13','A')
\n,('2021-06-04 11:45','B')
\n,('2021-06-04 15:02','C')
\n,('2021-06-08 23:02','A')
\n,('2021-06-09 03:04','E')<\/code><\/p>\nDECLARE @StartDate DATE, @EndDate DATE
\nSET @StartDate = '2021-11-01'
\nSET @EndDate = '2021-11-08'
\n;WITH cte AS
\n(\u00a0\u00a0\u00a0 SELECT @StartDate AS sDate
\nUNION ALL
\nSELECT DATEADD(DAY,1,sDate)
\nFROM cte
\nWHERE sDate < @EndDate
\n)
\nSELECT\u00a0 sDate
\nFROM cte;<\/code><\/p>\nDECLARE @startdate DATETIME = '2021-06-01'
\nDECLARE @endDate DATETIME = '2021-06-10'
\n;WITH cte
\nAS
\n(
\nSELECT @startdate as sDate
\nUNION All
\nSELECT DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) <= @endDate
\n)
\nSELECT
\nC.OriginalDate
\n,C.total
\nFROM
\n(
\nSELECT CONVERT(varchar(10),A.sDate,111) AS OriginalDate, COUNT(B.call_time) as total
\nFROM cte A
\nLEFT OUTER JOIN Test1 B
\nON A.sDate = CONVERT(varchar(10),B.call_time,111)
\nGROUP by CONVERT(varchar(10),A.sDate,111)
\n) C
\nORDER BY C.OriginalDate<\/code><\/p>\n