SQL - Trouble with nested query - Need to average counts over a specified date range

I am having trouble getting a nested query to behave as I'd like in Microsoft SQL Server Management Studio 19 *. Here is what the base table looks like. Each entry represents an internal order of a piece of inventory.

ID	Workcell	PartNum	EnteredOn	BadgeNumber
1	Workcell1	511111A	2023-08-01 13:28:04.240	1234
2	Workcell2	591222B	2023-08-01 14:10:05.100	1234
3	Workcell1	591222B	2023-08-02 13:09:51.855	0505
4	Workcell1	610000A	2023-08-02 14:19:20.050	9876

Here is the "inside" query, which gives a result like this.

SELECT
    DATEPART(HOUR, EnteredOn) AS [hr],
    COUNT(*) AS [cnt],
    DATEPART(DAY, EnteredOn) AS 'Day',
    DATEPART(MONTH, EnteredOn) AS 'Month',
    DATEPART(YEAR, EnteredOn) AS 'Year'
FROM 
    MyInventoryOrdersTable
WHERE
    Workcell = 'Workcell1' 
    AND EnteredOn BETWEEN '2023-08-01 00:00:00.000' AND '2023-08-03 00:00:00.000'
GROUP BY
    DATEPART(HOUR, EnteredOn),
    DATEPART(DAY, EnteredOn),
    DATEPART(MONTH, EnteredOn),
    DATEPART(YEAR, EnteredOn)

I'd like to be able to enter any date range (this will be done separately, through Python / Ignition 8.1 -- already have this part figured out) in order to have SQL return an average "count" for each hour across that date range -- this would be averaged over however many days the "inside" query above finds. So we could see our usual "busy times" of the day...

Here is how far I got:

I tried wrapping the entire query with this at the beginning...

SELECT MAX([hr]) AS 'Hour', AVG([cnt]) AS 'Average'
FROM (

...and this at the end...

)
r;

...But that just gives me the last hour's data, not an average for each hour from 0 to 23. I then got more lost when I realized:

I will need days without any data to provide a value of "0" when calculating this average. For example, if I grab a week-long chunk of data where one or more of the days had 0 output, I need the query to recognize that and take that into account when averaging the output for that week, even if it is not grabbing any entries to reflect this. I don't know the best way to do this.

Wondering if any SQL experts can help point me in the right direction. My expected results table would look like this for the example data provided at the top of this post:

Hour	Average
0	0.00
1	0.00
2	0.00
3	0.00
4	0.00
5	0.00
6	0.00
7	0.00
8	0.00
9	0.00
10	0.00
11	0.00
12	0.00
13	1.00
14	0.50
15	0.00
16	0.00
17	0.00
18	0.00
19	0.00
20	0.00
21	0.00
22	0.00
23	0.00

Solution

We can use following SQL SERVER query to get your desired output.

SELECT HOUR, FORMAT(SUM(Average),'N2') Average FROM
( SELECT DATEPART(HOUR, EnteredOn) Hour,
CAST(COUNT(1) AS FLOAT)/DATEDIFF(day,'2023-08-01 00:00:00.000', '2023-08-03 00:00:00.000') Average 
FROM MyInventoryOrdersTable
WHERE Workcell = 'Workcell1' AND EnteredOn BETWEEN '2023-08-01 00:00:00.000' AND '2023-08-03 00:00:00.000'
GROUP BY DATEPART(HOUR, EnteredOn)
UNION ALL
SELECT Hour, 0.00 Average from
(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) as HourList(Hour)
) OutputTable
GROUP BY Hour
ORDER BY Hour;

CAST(COUNT(1) AS FLOAT) will fetch total number of Orders in a specific hour and cast as float to get floating division result. DATEDIFF(day,'2023-08-01 00:00:00.000', '2023-08-03 00:00:00.000') - It calculates number of days in date range. Number of orders in a specific hour/Number of days will be equal to average of orders in a specific hour during given date range. HourList provides us all hours of day and 0.00 as average, As adding 0 to actual average will not make any difference.
By using UNION ALL and GROUP BY, we can get average for all hours of day.

We can check my execution and output on SQLFiddle

Excluded code:- We can use scaler variable to store start date and end date.

DECLARE @startdate DATETIME = '2023-08-01 00:00:00.000';  
DECLARE @enddate   DATETIME = '2023-08-03 00:00:00.000';