Is it possible to group counts by hour in MS Access and ensure any hours without values are listed in results with 0?

perspiringprogrammer 0
Viewed 39

Here is where I am at so far with a query. The submitted field is a date/time field that records the time and date a ticket was submitted. The id is a unique identifier for each ticket. I need to count the number of tickets submitted every hour for each day of the year. The table contains all tickets for a single year. For any hours where no tickets were submitted, I need to list a 0 value, which is where I am having a problem.

SELECT DateValue(submitted) AS [date], DatePart("h", submitted) AS [hour], nz(COUNT(id), 0) AS [total_tickets]
FROM table
GROUP BY DateValue(submitted), DatePart("h", submitted);

The results are not what I need:

date hour total_tickets
01-Jan-21 0 1
01-Jan-21 1 5
01-Jan-21 2 1
01-Jan-21 5 1
01-Jan-21 6 12
01-Jan-21 8 1

No tickets were submitted in the hours of 3, 4, & 7, thus no values are included in the results. However, I need those missing hours to be displayed with corresponding 0s in the total_tickets field. I am having a hard time finding a way to do this in Access SQL. Any help would be greatly appreciated.

group-by ms-access sql
2 Answers

This is not trivial. Typically you use number table(s) which you LEFT JOIN with your actual data.

Here is an explanation of the concept (in SQL Server you can create such a table on the fly with SQL, in Access you would use VBA and store it permamently):
https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

So in your case I would create:

nbrDate:

nDate
01-Jan-21
02-Jan-21
03-Jan-21
etc.

nbrHour:

nHour
0
1
2
...
23

With these you build a query that does a cartesian product of dates * hours, giving a row for each combination (i.e. a row for each hour of each day):

SELECT d.nDate, h.nHour
FROM nbrDate d, nbrHour h

And finally you LEFT JOIN the result with your original query, returning either the total_tickets from your query or 0 if there wasn't a matching row:

SELECT n.nDate, n.nHour, Nz(q.total_tickets, 0) AS total_tickets
FROM nbrQuery n LEFT JOIN yourQuery q 
ON n.nDate = q.date AND n.nHour = q.hour
[WHERE n.nDate is in the range you want to report]

Note that this is all air code.

userTQ1g0

You can use multiplying query (Cartesian join) to generate the hours, then join these with those submitted:

SELECT 
    query1.datehour, 
    Count(table.id) AS total_hours
FROM 

(SELECT DISTINCT 
    10 * Abs([Deca].[id] Mod 10) + Abs([Uno].[id] Mod 10) AS Id, 
    DateAdd("h",[Id], DateValue(submitted)) AS datehour
FROM 
    [table], 
    MSysObjects AS Uno, 
    MSysObjects AS Deca
WHERE 
    (10*Abs([Deca].[id] Mod 10) + Abs([Uno].[id] Mod 10)) < 24) AS query1 

LEFT JOIN 
    [table] 
    ON query1.datehour = table.submitted
GROUP BY 
    query1.datehour;

Output:

datehour            total_hours
------------------- -----------
01-01-2021           1
01-01-2021 01:00:00  5
01-01-2021 02:00:00  1
01-01-2021 03:00:00  0
01-01-2021 04:00:00  0
01-01-2021 05:00:00  1
01-01-2021 06:00:00 12
01-01-2021 07:00:00  0
01-01-2021 08:00:00  1
01-01-2021 09:00:00  0
01-01-2021 10:00:00  0
01-01-2021 11:00:00  0
01-01-2021 12:00:00  0
01-01-2021 13:00:00  0
01-01-2021 14:00:00  0
01-01-2021 15:00:00  0
01-01-2021 16:00:00  0
01-01-2021 17:00:00  0
01-01-2021 18:00:00  0
01-01-2021 19:00:00  0
01-01-2021 20:00:00  0
01-01-2021 21:00:00  0
01-01-2021 22:00:00  0
01-01-2021 23:00:00  0
uservDNe0
Related