Query to get results for every hour of the day eve

Question

I am trying to get the query for getting the count of users every hour of day in the table. If the data for that hour is not present, I want to record the hour with count of zero. Also users should be counted only for their first entry. Subsequent entries should be ignored.

Table:

userId      creationDate
1           2014-10-08 14:33:20.763
2           2014-10-09 04:24:14.283
3           2014-10-10 18:34:26.260

Desired output:

Date                      UserCount
2014-10-08 00:00:00.000      1
2014-10-08 01:00:00.000      1
2014-10-08 02:00:00.000      1
2014-10-08 03:00:00.000      0
2014-10-08 04:00:00.000      1
....
.....
2014-10-10 23:00:00.000      1
2014-10-10 00:00:00.000      0

My attempt:

SELECT 
    CAST(creationDate as date) AS ForDate, 
    DATEPART(hour, date) AS OnHour, 
    COUNT(distinct userId) AS Totals
FROM 
    Table
WHERE
    primaryKey = 123
GROUP BY 
    CAST(creationDate as date), DATEPART(hour, createDate)

This only gives me per hour for the record that is present. Not the data for the missing hours. I think there is a way by using a cross join to get 0 data even for the missing hours.

Something like this, I came across, but not able to construct a proper query with it.

cross join (select 
                ROW_NUMBER() over (order by (select NULL)) as seqnum
            from 
                INFORMATION_SCHEMA.COLUMNS) hours 
where hours.seqnum >= 24 

Once again, I am not a SQL expert, but trying hard to construct this result set.

One more attempt :

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour 
     from (select distinct cast(cast(createDate as DATE) as datetime) as thedatehour
           from Table a 
          ) a 
          cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours.seqnum (less than)= 24
    )
select dh.DateHour, COUNT(distinct c.userId) 
from dh cross join Table c
--on dh.DateHour = c.createDate
group by dh.DateHour
order by 1

Answer 1

I asked a similar question on dba just this morning...https://dba.stackexchange.com/questions/86435/filling-in-date-holes-in-grouped-by-date-sql-data. You can used my GetSequence function, or create a Numbers table. I haven't done my own testing yet to validate what was suggested in my scenario.

Answer 2

Try this:

BUILD SAMPLE DATA

CREATE TABLE yourTable(
    userId          INT,
    creationDate    DATETIME
)
INSERT INTO yourTable VALUES (1, '2014-10-08 14:33:20.763'), (2, '2014-10-09 04:24:14.283'),(3, '2014-10-10 18:34:26.260');

SOLUTION

WITH tally(N) AS(
    SELECT TOP(23) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns
)
,hourly(creationDate) AS(
    SELECT DATEADD(HOUR, t.N, d.creationDate)
    FROM tally t
    CROSS JOIN(
        SELECT DISTINCT DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0) AS creationDate FROM yourTable
    ) d
)
SELECT
    h.creationDate,
    userCount = ISNULL(t.userCount, 0)
FROM hourly h
LEFT JOIN(
    SELECT
        creationDate = DATEADD(HOUR, DATEPART(HOUR, creationDate) ,DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0)),
        userCount = COUNT(*)
    FROM yourTable
    GROUP BY DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0), DATEPART(HOUR, creationDate)
)t
    ON t.creationDate = h.creationDate

CLEANUP

DROP TABLE yourTable

Answer 3

1. Create a temporary table (let's say #CreationDateHours) containing create date and hours from 0 to 23.

   Declare @date as date
   SELECT MAX(CAST(creationDate as date)) AS ForDate, 0 as OnHour into #CreationDateHours
       FROM Table
           WHERE
              primaryKey = 123
       Select @date=ForDate from #CreationDateHours 
       Declare @i int
       Set @i=1
       While @i<24
       begin
       insert into #CreationDateHours
       select @date as ForDate, @i as OnHour 
       set @i+=1
       end
   

2. Now, Run this query to get the desired results

   select t1.ForDate, t1.OnHour, isnull(t2.Totals,0) AS Totals
   from
       #CreationDateHours t1 left join (SELECT 
           CAST(creationDate as date) AS ForDate, 
           DATEPART(hour, date) AS OnHour, 
           COUNT(distinct userId) AS Totals
       FROM 
           Table
       WHERE
           primaryKey = 123
       GROUP BY 
           CAST(creationDate as date), DATEPART(hour, createDate)) as t2
   on t1.ForDate= t2.ForDate and t1.OnHour=t2.OnHour
   

Answer 4

You need to build up a table of possible hours, and then join this to your actual records.

The best way to build up a table of possible hours is to use a recursive common table expression. Here's how:

-- Example data
DECLARE @users TABLE(UserID INT, creationDate DATETIME)
INSERT @users
        ( UserID, creationDate )
VALUES  ( 1, '2014-10-08 14:33:20.763'),
        (  2, '2014-10-09 04:24:14.283'),
         ( 3, '2014-10-10 18:34:26.260')


;WITH u1st AS (  -- determine the FIRST time the user appears
    SELECT UserID, MIN(creationDate) AS creationDate
    FROM @users
    GROUP BY UserID
),  hrs AS ( -- recursive CTE of start hours
    SELECT DISTINCT CAST(CAST(creationDate AS DATE) AS DATETIME) AS [StartHour] 
    FROM @users AS u
    UNION ALL
    SELECT DATEADD(HOUR, 1, [StartHour]) AS [StartHour] FROM hrs 
    WHERE DATEPART(HOUR,[StartHour]) < 23
), uGrp AS ( -- your data grouped by start hour
    SELECT -- note that DATETIMEFROMPARTS is only in SQL Server 2012 and later
        DATETIMEFROMPARTS(YEAR(CreationDate),MONTH(CreationDate), 
                     DAY(creationDate),DATEPART(HOUR, creationDate),0,0,0) 
                     AS StartHour, 
        COUNT(1) AS UserCount  FROM u1st AS u
    GROUP BY YEAR(creationDate), MONTH(creationDate), DAY(creationDate), 
             DATEPART(HOUR, creationDate)
)
SELECT hrs.StartHour, ISNULL(uGrp.UserCount, 0) AS UserCount 
FROM hrs LEFT JOIN uGrp ON hrs.StartHour = uGrp.StartHour
ORDER BY hrs.StartHour

NB - DATETIMEFROMPARTS is only in SQL SERVER 2012 and greater. If you are using an earlier version of SQL SERVER you could have

WITH u1st AS ( -- determine the FIRST time the user appears
    SELECT UserID, MIN(creationDate) AS creationDate
    FROM @users
    GROUP BY UserID
),  hrs AS ( -- recursive CTE of start hours
    SELECT DISTINCT CAST(CAST(creationDate AS DATE) AS DATETIME) AS [StartHour] 
    FROM @users AS u
    UNION ALL
    SELECT DATEADD(HOUR, 1, [StartHour]) AS [StartHour] FROM hrs 
    WHERE DATEPART(HOUR,[StartHour]) < 23
), uGrp AS ( -- your data grouped by start hour
    SELECT -- note that DATETIMEFROMPARTS is only in SQL Server 2012 and later
        CAST(CAST(YEAR(creationDate) AS CHAR(4)) + '-'
             + RIGHT('0' + CAST(MONTH(creationDate) AS CHAR(2)), 2) + '-'
             + RIGHT('0' + CAST(DAY(creationDate) AS CHAR(2)), 2) + ' '
             + RIGHT('0' + CAST(DATEPART(HOUR, creationDate) AS CHAR(2)), 2) 
             + ':00:00.000'
             AS DATETIME) AS StartHour,
        COUNT(1) AS UserCount  FROM u1st AS u
    GROUP BY YEAR(creationDate), MONTH(creationDate), DAY(creationDate), 
             DATEPART(HOUR,creationDate)
)
SELECT hrs.StartHour, ISNULL(uGrp.UserCount, 0) AS UserCount 
FROM hrs LEFT JOIN uGrp ON hrs.StartHour = uGrp.StartHour
ORDER BY hrs.StartHour