SQL Query to return 24 hour, hourly count even when no values exist?
Solution 1
So going back to using Remus' original function, I've re-used it in a recursive call and storing the results in a temp table:
DECLARE @count INT
DECLARE @NumDays INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CurrentDay DATE
DECLARE @tmp_Transactions TABLE
(
StartHour DATETIME,
TotalHourlyTransactions INT
)
SET @StartDate = '2000/02/10'
SET @EndDate = '2010/02/13'
SET @count = 0
SET @NumDays = DateDiff(Day, @StartDate, @EndDate)
WHILE @count < @NumDays
BEGIN
SET @CurrentDay = DateAdd(Day, @count, @StartDate)
INSERT INTO @tmp_Transactions (StartHour, TotalHourlyTransactions)
SELECT h.StartHour ,
t.TotalHourlyTransactions
FROM tvfGetDay24Hours(@CurrentDay) AS h
OUTER APPLY ( SELECT COUNT(TransactionID) AS TotalHourlyTransactions
FROM [dbo].[TerminalTransactions]
WHERE TransactionTime BETWEEN h.StartHour AND h.EndHour
AND TerminalId = 4
) AS t
ORDER BY h.StartHour
SET @count = @Count + 1
END
SELECT *
FROM @tmp_Transactions
Solution 2
You do this by building first the 23 hours table, the doing an outer join against the transactions table. I use, for same purposes, a table valued function:
create function tvfGetDay24Hours(@date datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@date as float)) as datetime)) as StartHour
, dateadd(hour, number+1, cast(floor(cast(@date as float)) as datetime)) as EndHour
from master.dbo.spt_values
where number < 24 and type = 'p');
Then I can use the TVF in queries that need to get 'per-hour' basis data, even for missing intervals in the data:
select h.StartHour, t.TotalHourlyTransactions
from tvfGetDay24Hours(@StartDate) as h
outer apply (
SELECT
COUNT(TransactionID) AS TotalHourlyTransactions
FROM MyTransactions
WHERE TransactionTime BETWEEN h.StartHour and h.EndHour
AND TerminalId = @TerminalID) as t
order by h.StartHour
Updated
Example of a TVF that returns 24hours between any arbitrary dates:
create function tvfGetAnyDayHours(@dateFrom datetime, @dateTo datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@dateFrom as float)) as datetime)) as StartHour
, dateadd(hour, number+1, cast(floor(cast(@dateFrom as float)) as datetime)) as EndHour
from master.dbo.spt_values
where type = 'p'
and number < datediff(hour,@dateFrom, @dateTo) + 24);
Note that since master.dbo.spt_values contains only 2048 numbers, the function will not work between dates further apart than 2048 hours.
Solution 3
You have just discovered the value of the NUMBERS table. You need to create a table with a single column containing the numbers 0 to 23 in it. Then you join again this table using an OUTER join to ensure you always get 24 rows returned.
ElHaix
Solutions architect in a drive for consistently learning and assisting when possible.
Updated on July 09, 2022Comments
-
ElHaix almost 2 years
I've written a query that groups the number of rows per hour, based on a given date range.
SELECT CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) as TDate, COUNT(TransactionID) AS TotalHourlyTransactions FROM MyTransactions WITH (NOLOCK) WHERE TransactionTime BETWEEN CAST(@StartDate AS SMALLDATETIME) AND CAST(@EndDate AS SMALLDATETIME) AND TerminalId = @TerminalID GROUP BY CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) ORDER BY TDate ASC
Which displays something like this:
02/11/20 07 4 02/11/20 10 1 02/11/20 12 4 02/11/20 13 1 02/11/20 14 2 02/11/20 16 3
Giving the number of transactions and the given hour of the day.
How can I display all hours of the day - from 0 to 23, and show 0 for those which have no values?
Thanks.
UPDATE
Using the tvf below works for me for one day, however I'm not sure how to make it work for a date range.
Using the temp table of 24 hours:
-- temp table to store hours of the day DECLARE @tmp_Hours TABLE ( WhichHour SMALLINT ) DECLARE @counter SMALLINT SET @counter = -1 WHILE @counter < 23 BEGIN SET @counter = @counter + 1 --print INSERT INTO @tmp_Hours ( WhichHour ) VALUES ( @counter ) END SELECT MIN(CONVERT(VARCHAR(10),[dbo].[TerminalTransactions].[TransactionTime],101)) AS TDate, [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) AS TheHour, COUNT([dbo].[TerminalTransactions].[TransactionId]) AS TotalTransactions, ISNULL(SUM([dbo].[TerminalTransactions].[TransactionAmount]), 0) AS TransactionSum FROM [dbo].[TerminalTransactions] RIGHT JOIN @tmp_Hours ON [@tmp_Hours].[WhichHour] = CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) GROUP BY [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108), COALESCE([dbo].[TerminalTransactions].[TransactionAmount], 0)
Gives me a result of:
TDate WhichHour TheHour TotalTransactions TransactionSum ---------- --------- ------- ----------------- --------------------- 02/16/2010 0 00 4 40.00 NULL 1 NULL 0 0.00 02/14/2010 2 02 1 10.00 NULL 3 NULL 0 0.00 02/14/2010 4 04 28 280.00 02/14/2010 5 05 11 110.00 NULL 6 NULL 0 0.00 02/11/2010 7 07 4 40.00 NULL 8 NULL 0 0.00 02/24/2010 9 09 2 20.00
So how can I get this to group properly?
The other issue is that for some days there will be no transactions, and these days also need to appear.
Thanks.