CODEWITHSUNDEEP
sql-servert-sql
Jerome Viveiros
Jerome Viveiros

Reputation: 241

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

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)

Upvotes: 0

Views: 3063

Answers (4)

Jerome Viveiros
Jerome Viveiros

Reputation: 241

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 |
+------------+----------+----------+

Upvotes: 1

paul
paul

Reputation: 22001

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

Upvotes: 0

Vasily
Vasily

Reputation: 5782

     --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)

Upvotes: 0

Svein Fidjest&#248;l
Svein Fidjest&#248;l

Reputation: 3206

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)

Upvotes: 2

Related Questions