SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')

6 rows affected

SELECT DATEPART(hh,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
GROUP BY DATEPART(hh, TempDateTime)


TempDatePart
RecordCount


8
2

10
1

12
1

16
1

23
1


SELECT 
h.h as DatePartHour, 
--DATEPART(hh,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(hh,TempDateTime)
--where h.h = DATEPART(hh,TempDateTime)
GROUP BY  h.h


DatePartHour
RecordCount


0
0

1
0

2
0

3
0

4
0

5
0

6
0

7
0

8
2

9
0

10
1

11
0

12
1

13
0

14
0

15
0

16
1

17
0

18
0

19
0

20
0

21
0

22
0

23
1


Warning: Null value is eliminated by an aggregate or other SET operation.

DatePartHour	RecordCount
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	2
9	0
10	1
11	0
12	1
13	0
14	0
15	0
16	1
17	0
18	0
19	0
20	0
21	0
22	0
23	1

DatePartHour	RecordCount
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	2
9	0
10	1
11	0
12	1
13	0
14	0
15	0
16	1
17	0
18	0
19	0
20	0
21	0
22	0
23	1

DatePartHour	RecordCount
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	2
9	0
10	1
11	0
12	1
13	0
14	0
15	0
16	1
17	0
18	0
19	0
20	0
21	0
22	0
23	1