snippetsqlMinor
How to return 0 for record count when nothing returns for that hour using datepart?
Viewed 0 times
returnhourrecordnothingforreturnshowthatcountusing
Problem
In my sample query below, I am pulling the count by hour correctly, but I need it to return all hours of the day and display 0 for the hours that have no data. Is there an easy way for me to do this?
Here is my SQL:
Here is what I'm looking for:
Here is my SQL:
IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL DROP TABLE #Temp;
CREATE TABLE #Temp (TempNumber NVARCHAR(50),TempDateTime DATETIME2)
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233600','2019-08-20 08:27:08.047')
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233601','2019-08-20 08:32:08.047')
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233602','2019-08-20 10:27:08.047')
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233603','2019-08-20 12:27:08.047')
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233604','2019-08-20 16:27:08.047')
INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233605','2019-08-20 23:27:08.047')
SELECT DATEPART(hh,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
GROUP BY DATEPART(hh, TempDateTime)Here is what I'm looking for:
Solution
You can use a table constructor of all hours (0-23) and join to that from your
DB FIDDLE
DATEPART function.DB FIDDLE
SELECT
h.h as DatePartHour,
--DATEPART(hour,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
full join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0)) h(h)
on h.h = DATEPART(hour,TempDateTime)
GROUP BY h.hCode Snippets
SELECT
h.h as DatePartHour,
--DATEPART(hour,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
full join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0)) h(h)
on h.h = DATEPART(hour,TempDateTime)
GROUP BY h.hContext
StackExchange Database Administrators Q#246461, answer score: 5
Revisions (0)
No revisions yet.