count rows by hour and include zeroes

Question

I am trying to count (by hour) the number of entries into a database. I have successfully written a query that counts by hour, but it omits hours with zero entries. I need the results to include the zeroes. I have looked around the internet, and have found a lot of suggestions. I have created a View that has one column with datetime entries by minute. I've tried joining the main table to this view and I get the same results as without the join. Still no zeroes. Wondering how to get this query to return zeroes. I am using MS SQL 2008 R2. any suggestions?

declare @limit datetime;
use InputArchive
set @limit = current_timestamp;
set @limit = DATEADD(hour, -72, @limit);
SELECT DATEADD(hour, datediff(hour, 0, ArchivedItems.RecordCreated), 0) as TimeHour, COUNT(ISNULL((ArchivedItems.RecordCreated),' ')) as NumPerHour
FROM ArchivedItems 
LEFT OUTER JOIN vw_hoursalot
ON vw_hoursalot.dtHr = ArchivedItems.RecordCreated
where InputTypeId = 5 or InputTypeId = 6 or InputTypeId = 8 and (ArchivedItems.RecordCreated >= @limit)
Group BY DATEADD(hour, Datediff(hour, 0, ArchivedItems.RecordCreated), 0)
order by DATEADD(hour, datediff(hour, 0, ArchivedItems.RecordCreated), 0) desc
option (MAXRECURSION 0)

Update: I changed the hoursalot view to be by the hour I'm sorry but I'm not sure what you mean by full view SQL.

I can't put any strait info from the archivedItems table for legal reasons but the RecordCreated column is a strait timestamp ie '2013-04-05 14:09:59.167'

Answer 1

Here is what I came up with:

declare @limit datetime;
declare @BaseTime datetime
set @BaseTime = '20141020 15:00'; --must be an even hour
set @limit = DATEADD(hour, -72, @BaseTime);
print @Basetime
;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  Numbers AS (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS Number FROM D2),
  AllHours AS (SELECT DATEADD(hour,numbers.number,@limit) AS hr FROM Numbers),
  Raw_Data AS (
    SELECT DATEADD(hour,DATEDIFF(hour,@BaseTime,RecordCreated),@BaseTime) AS HourRecorded FROM
    ArchivedItems
    WHERE RecordCreated BETWEEN @limit AND @BaseTime
    AND InputTypeID IN (5,6,8)
    )
SELECT count(Raw_Data.HourRecorded),AllHours.hr
FROM AllHours left outer join Raw_Data on AllHours.hr = Raw_Data.HourRecorded
GROUP BY AllHours.hr
order by AllHours.hr

Here is what I used to create the test data

;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b),
  Numbers AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS Number FROM D4)
INSERT INTO ArchivedItems(InputTypeID, RecordCreated) 
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) % 10 AS INT)), DATEADD(MINUTE, ABS(CAST(CAST(NEWID() AS VARBINARY) % 10000 AS INT)),'20141017')
FROM Numbers
--Make sure there is a gap
DELETE FROM ArchivedItems WHERE RecordCreated BETWEEN '2014-10-20 06:00:00.000' AND '2014-10-20 08:00:00.000'

Answer 2

Try putting the vw_hoursalot as the leftmost table in the condition - this will mean that all rows from the view will be returned, whether a corresponding record in ArchivedHours is found or not.

I've edited again - this time I took the time to mock up some dummy data, and I realized that a problem in the code, both mine and yours, is that the ArchivedItems.RecordCreated column is being used as a filter in the WHERE condition. Therefore, only records with an existing RecordCreated value will be returned.

I've moved that condition to the JOIN, and run the query below against some very basic data I mocked up. Assuming that your vw_HoursALot view is returning twenty-four integers numbered 0-23, this ought to get you the data you are looking for.

PLEASE NOTE: I made the assumption that InputTypeID is from ArchivedItems.

WITH -- I used these two CTEs as my dummy data, based on the information in your post
    vw_HoursALot AS 
      (
      SELECT 1 dtHr UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
      SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION 
      SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION 
      SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION 
      SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 0 
      ),
    ArchivedItems AS 
      (
        SELECT GETDATE() AS RecordCreated, 5 AS InputTypeID
        UNION SELECT DATEADD(HOUR, -3, GETDATE()), 6 AS InputTypeID
      )


-- this part is actually doing the work
SELECT 
    vw_HoursALot.dtHr,
    COUNT(ArchivedItems.RecordCreated) AS NumPerHour 
FROM 
    vw_hoursalot
     LEFT OUTER JOIN 
    ArchivedItems ON 
        vw_hoursalot.dtHr = DATEPART(hour, ArchivedItems.RecordCreated) AND
        ArchivedItems.RecordCreated >= DATEADD(hour, -72, GETDATE()) AND
          (
            InputTypeId = 5 OR
            InputTypeId = 6 OR 
            InputTypeId = 8 
          ) 
GROUP BY vw_HoursALot.dtHr 
ORDER BY vw_HoursALot.dtHr DESC 
OPTION (MAXRECURSION 0)