SQL to display the all the non transactional hrs

Question

I'm displaying the number of transactions every hour, which works great. However, now I have a requirement to display that hr on which 0 transactions are occurred along with the hrs transaction exist. so display the hr number and display 0 if there no transaction count in that hr.

Not sure if I was able to explain correctly for which I'm really sorry(if I dont) but the below SQL statement and output would help you understand what I'm trying to achieve.

SQL Query I'm running on SQL Server is

   SELECT CAST([transOcurredOn] AS DATE) as Dategg, 
   DATEPART(HH,transOcurredOn) as 'hour',
   COUNT(*) AS 'Trans count'
   FROM [tpTrans]
   WHERE [transOcurredOn] > '2016-06-18' 
   GROUP BY CAST([transOcurredOn] AS DATE) ,DATEPART(HH,transOcurredOn)
   ORDER BY Dategg,hour

So my Current output looks like

my new output should look like below, to cover the whole 7 to 23 hrs clock. I highlighted the change

I am bit struggling, appreciate if someone points me in the right direction

Blockquote

ing, app

Create a table of possible hours (24 rows), either [dynamically](https://stackoverflow.com/q/21425546/11683) or as an actual table, and left join your existing query with it. If you also have days that don't have transactions at all, also generate a list of possible days, cross join that to possible hours, then left join the result with your existing query. — GSerg, Feb 26 '19 at 18:43
not sure if I understood correctly but how I can join the temp table where there is no relationship to join. For e.g. if I create a temp table to populate the count 1 to 24, How I can display the same continuously on the different date without putting in a loop.? I'm running for whole year and I dont know which hr have no transactions. Its a 15 hrs operation — Cortez Ninja, Feb 26 '19 at 18:49
so i created the temp table, inserted the working hrs from 7 to 23 hrs and add the cross join and it mess it up the transaction count output. I tried adding the hr column into the query and its been 3 mins query is still running. So I kill it. SELECT CAST([transOcurredOn] AS DATE) as Dategg, DATEPART(HH,transOcurredOn) as 'hour', z.hrcount COUNT(*) AS 'Trans count' FROM [tpTrans] cross join zz_temptable z WHERE [transOcurredOn] > '2016-06-18' GROUP BY CAST([transOcurredOn] AS DATE) ,DATEPART(HH,transOcurredOn),z.hourcount ORDER BY Dategg,hour — Cortez Ninja, Feb 26 '19 at 19:08
No, you don't cross join possible hours with actual transactions. You cross join possible hours with possible days, then you left join the result with the actual transactions on equality of both day and time. — GSerg, Feb 26 '19 at 19:20

score 1 · Accepted Answer · answered Feb 26 '19 at 20:34

You can create a calendar table for the dates, and a tally table for the hours. Use a CROSS JOIN between those tables to generate records for the 24 hours of the day of all your possible dates, and then LEFT JOIN your table onto that. The final query would look something like

   SELECT c.Date as Dategg, t.num as 'hour',
            COUNT(transOcurredOn) AS 'Trans count'
   FROM Calendar c
   CROSS JOIN Tally t
   LEFT JOIN [tpTrans] 
            ON t.num = DATEPART(HH,transOcurredOn)
            AND c.Date = CONVERT(DATE, [transOcurredOn] )
    WHERE c.Date> '2016-06-18' and c.Date <= GETDATE()
        AND t.num <=24
    GROUP BY c.Date ,t.num
    ORDER BY c.Date, t.num

score 1 · Answer 2 · answered Feb 26 '19 at 23:26

Create a table valued function and call it to create a table of Dates and Hours;

SELECT CONVERT(varchar(10),[Start_Date],101)'Date', DATEPART(HOUR,[Start_Date])'Hour' 
FROM generateDateTable('1/1/2019','1/12/2019','hour',1)
WHERE DATEPART(HOUR,[Start_Date]) >= 7

Function definition;

CREATE function [dbo].[generateDateTable] 
(
@start_date datetime
, @end_date datetime
, @datepart varchar(20) = 'day'
, @step int = 1
)
returns @dates table 
(
start_date datetime, 
end_date datetime
)
as
begin
if( @datepart in ('year', 'yy', 'yyyy', 'quarter', 'qq', 'q', 'month', 'mm', 'm', 
'dayofyear', 'dy', 'y', 'day', 'dd', 'd', 'week', 'wk', 'ww') )
begin
    set @start_date = cast(floor(cast(@start_date as float)) as datetime)
    set @end_date = cast(floor(cast(@end_date as float)) as datetime)
end

declare @new_start datetime

while @start_date <= @end_date
begin
    set @new_start = (case 
        when @datepart in ('year', 'yy', 'yyyy') then dateadd(yy, @step, @start_date)
        when @datepart in ('quarter', 'qq', 'q') then dateadd(qq, @step, @start_date)
        when @datepart in ('month', 'mm', 'm') then dateadd(mm, @step, @start_date) 
        when @datepart in ('dayofyear', 'dy', 'y') then dateadd(dy, @step, 
 @start_date) 
        when @datepart in ('day', 'dd', 'd') then dateadd(dd, @step, @start_date) 
        when @datepart in ('week', 'wk', 'ww') then dateadd(ww, @step, @start_date) 
        when @datepart in ('hour', 'hh') then dateadd(hh, @step, @start_date) 
        when @datepart in ('minute', 'mi', 'n') then dateadd(n, @step, @start_date) 
        when @datepart in ('second', 'ss', 's') then dateadd(s, @step, @start_date) 
        when @datepart in ('millisecond', 'ms') then dateadd(ms, @step, @start_date) 
        else dateadd(dd, @step, @start_date)
    end)
        insert 
            @dates 
        (
          start_date
        , end_date
        ) values (
            @start_date
            , dateadd(ms, -3, @new_start)
        )
        set @start_date = @new_start
    end
    return 
end

SQL to display the all the non transactional hrs

2 Answers2