SQL Select total count per day, but group by date and hour (running total per hour)

Question

I've found something similar to what I want here, but it doesn't give exactly the right output, and I'm having a stupid moment trying to think of the correct solution...

We have an AVS server (real-time account verification service, but this is irrelevant), and I want to know how many requests have been made today (or any other day), at each hour. For example, let's say 100 requests were made between 8 and 9 AM, and 50 were made between 9 and 10AM. The output should have 100 at 8AM, and 150 at 9AM, but my query below is displaying the number of requests in each hour instead.

I can see why it is doing this... It's grouping by hour and so it shows me the requests in each hour, but that's not what I want, which is a running total per hour. (So I can run it each day and see what the number of requests looked like this time yesterday, etc... and get an indication if this is a slow or fast day.) How do I do this?

DECLARE @CurrentDate DATETIME = '14 nov 2014'

SELECT 
    CAST(DateCreated AS DATE) [Date], 
    DATEPART(hour,DateCreated) [Hour],
    COUNT(ID) [Requests] 
FROM Request 
WHERE 
    DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate)
GROUP BY
    CAST(DateCreated AS DATE),
    DATEPART(hour, DateCreated)
ORDER BY 
    CAST(DateCreated AS DATE)

score 2 · Answer 1 · answered Nov 14 '14 at 12:47

2

The following should work

DECLARE @CurrentDate DATETIME = '13 nov 2014'

SELECT DISTINCT 
    CAST(DateCreated AS DATE) [Date], 
    DATEPART(hour,DateCreated) [Hour],
    COUNT(ID) OVER (ORDER BY DATEPART(hour, DateCreated))
FROM Request
WHERE 
    DateCreated >= @CurrentDate AND DateCreated < DATEADD(DAY, 1, @CurrentDate)
ORDER BY 
    DATEPART(hour, DateCreated)

answered Nov 14 '14 at 12:47

Svein Fidjestøl

3,106
2
24
40

Not sure what version of SQL this is, but I'm going to accept this one... However, it only works for me if I change the query to use the rank() function. so it uses "rank() OVER (ORDER BY DATEPART(hour, DateCreated))" – Jerome Viveiros Nov 14 '14 at 13:06
This is for SQL Server 2008 R2. – Svein Fidjestøl Nov 14 '14 at 13:39

Jerome Viveiros · Accepted Answer · 2014-11-17T15:33:35.463

Thank you for all the answers. None of them did exactly what I want though, so here is my final query, in case it may help someone. The problem with the answers, and also possibly my question, was that the results only show the total requests each hour, but not the final total for the day. My query below gives the requests per hour, as well as the final requests for the day, or the current total if it is the current day.

DECLARE @CurrentDate DATETIME = '17 Nov 2014'

SELECT * FROM ( 
    SELECT DISTINCT 
        CAST(DateCreated AS DATE) [Date],
        CASE
            WHEN LEN(CAST(DATEPART(hour,DateCreated) AS VARCHAR)) = 1 THEN '0' + CAST(DATEPART(hour,DateCreated) AS VARCHAR)
            ELSE CAST(DATEPART(hour,DateCreated) AS VARCHAR)
        END + ':00:00' [Time],
        rank() OVER (ORDER BY DATEPART(hour, DateCreated))[Requests]
    FROM Request
    WHERE 
        DateCreated >= @CurrentDate AND DateCreated < DATEADD(DAY, 1, @CurrentDate)
    UNION   
    SELECT  
        CAST(@CurrentDate AS DATE),
        CONVERT(VARCHAR(8),
                (SELECT TOP 1 DateCreated FROM Request WHERE DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate) ORDER BY DateCreated DESC),
                108),
        (SELECT COUNT(*) from Request WHERE DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate))
        ) t
WHERE t.Requests > 0         
ORDER BY t.Time

Running the query today gives output like this:

+------------+----------+----------+
|    Date    |   Time   | Requests |
+------------+----------+----------+
| 2014-11-17 | 07:00:00 |        1 |
| 2014-11-17 | 08:00:00 |        9 |
| 2014-11-17 | 09:00:00 |       49 |
| 2014-11-17 | 10:00:00 |      113 |
| 2014-11-17 | 11:00:00 |      225 |
| 2014-11-17 | 12:00:00 |      294 |
| 2014-11-17 | 13:00:00 |      363 |
| 2014-11-17 | 13:09:46 |      383 |
+------------+----------+----------+

paul · Answer 3 · 2014-11-14T13:04:10.937

0

DECLARE @CurrentDate DATETIME = '14 nov 2014';

WITH hourValues(hourVal) as (
    select  distinct cast(convert(varchar(14), @CurrentDate, 120) + '00' as datetime)
    from    [Request]
    where   datediff(day, [DateCreated], @CurrentDate) = 0)
SELECT 
    CAST(DateCreated AS DATE) [Date], 
    hourVal,
    COUNT(case when [DateCreated] < dateadd(hour, 1, hourVal) then 1 else null end) [Requests] 
FROM Request, hourValues
WHERE 
    datediff(day, [DateCreated], @CurrentDate) = 0
GROUP BY
    CAST(DateCreated AS DATE),
    hourVal
ORDER BY 
    [Date],
    HourVal

edited Nov 14 '14 at 13:04

answered Nov 14 '14 at 12:42

paul

21,653
1
53
54

Is the Microsoft SQL server? Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'. – Jerome Viveiros Nov 14 '14 at 12:48
If I remove the closing bracket on line 15 and change [Requests] to [Request] this works, but doesn't give me quite what I want. It gives a single date, hour value and requests, but if I run it for yesterday's date, it gives me the total number of requests for the day. – Jerome Viveiros Nov 14 '14 at 12:51
Edited to fix typo - swapped `getdate()` for `@CurrentDate` in line 4 – paul Nov 14 '14 at 13:03

Vasily · Answer 4 · 2014-11-14T13:00:02.367

     --if you use MS SQL Server 2008 R2 or later version (2012/2014) then try this

DECLARE @CurrentDate DATE = CONVERT(DATE, GETDATE() - 1) , --yesterday
    @FinishDate AS DATE = CONVERT(DATE, GETDATE() + 1) --tomorrow

SELECT  DISTINCT
        CONVERT(DATE, DateCreated) AS [Date] ,
        DATEPART(HOUR, DateCreated) AS [Hour] ,
        COUNT(ID) OVER ( PARTITION BY CONVERT(DATE, DateCreated) ORDER BY DATEPART(HOUR,
                                                              DateCreated) ) AS [Cumulative Count]
FROM    Request
WHERE   CONVERT(DATE, DateCreated) BETWEEN @CurrentDate
                                   AND     @FinishDate
ORDER BY CONVERT(DATE, DateCreated) ,
        DATEPART(HOUR, DateCreated)

SQL Select total count per day, but group by date and hour (running total per hour)

4 Answers4