Query to return 24 hours and a month of hourly count even when no values exist

0
votes

enter image description here

I have these result below and all I want is that days and records of days that there is no count.

Thanks below is the query I used

SELECT dateadd(hour, datediff(hour, 0, date_sent_to_adobe), 0) as TimeStampHour, Count(*) 'Total Count this Hour' FROM [Publication_Data_Management].[AdobeExtract].[Membership_Detail] GROUP BY dateadd(hour, datediff(hour, 0, date_sent_to_adobe), 0) ORDER BY dateadd(hour, datediff(hour, 0, date_sent_to_adobe), 0) desc

is it because the way the query is written it will only show dates in the table , how can I rewrite the query to show date with every hour and also show hours where there is no count of records. see the attached picture.

I want to also date hour of records without a count of records.

2
sqlsql-servertsql
Which dbms are you using? When it comes to date/time many products are far from ANSI SQL compliant. (And your query contains some product specific functions.) – jarlh
using dbms 2014 – user9361077
AFAIK there exists no DBMS that is called 2014. Do you mean SQL Server 2014 maybe? – Thorsten Kettner
You are selecting timstamps truncated to the hour. So your results may contain multiple days. Is that desired or do you actually want an aggregation to 24 records only, one for each hour in the day regardless of the date? – Thorsten Kettner
Possible duplicate of count rows by hour and include zeroes – Tanner

2 Answers

1
votes

In order to display all hours count you need to do JOIN with look-up table which has all hours. 

SELECT tt.Hours, COALESCE(a.Counts , 0) [Total Count this Hour] 
FROM
(
   VALUES (0), (1), ...(24)
)tt (Hours) LEFT JOIN (   
    SELECT 
       dateadd(hour, datediff(hour, 0, date_sent_to_adobe), 0) as TimeStampHour, 
       Count(*) Counts 
    FROM [Publication_Data_Management].[AdobeExtract].[Membership_Detail] 
    GROUP BY dateadd(hour, datediff(hour, 0, date_sent_to_adobe), 0) 
) a ON datepart(hour, a.TimeStampHour) = tt.Hours

This assumes you are working with SQL Server

0
votes

If you want to display values that aren't inside your data, you need to generate another table set with the values, then do a LEFT JOIN. On the following 2 examples I use a recursive CTE to generate the hours and months before joining them (you could also generate them with a explicit VALUES expression).

-- Hours
;WITH HoursCTE AS
(
    SELECT
        Hour = 0

    UNION ALL

    SELECT
        Hour = C.Hour + 1
    FROM
        HoursCTE AS C
    WHERE
        C.Hour + 1 <= 23
)
SELECT
    H.Hour,
    COUNT(M.date_sent_to_adobe) 'Total Count this Hour' 
FROM
    HoursCTE AS H
    LEFT JOIN [Publication_Data_Management].[AdobeExtract].[Membership_Detail] AS M ON
        H.Hour = DATEPART(HOUR, M.date_sent_to_adobe)
GROUP BY
    H.Hour

-- Months
;WITH MonthCTE AS
(
    SELECT
        Month = 1

    UNION ALL

    SELECT
        Month = C.Month + 1
    FROM
        MonthCTE AS C
    WHERE
        C.Month + 1 <= 12
)
SELECT
    H.Month,
    COUNT(M.date_sent_to_adobe) 'Total Count this Month' 
FROM
    MonthCTE AS H
    LEFT JOIN [Publication_Data_Management].[AdobeExtract].[Membership_Detail] AS M ON
        H.Month = DATEPART(MONTH, M.date_sent_to_adobe)
GROUP BY
    H.MONTH