SQL how to sum events in one hour between two dates and show it in one row

Question

I am developing a report with C# and SQL server 2005, I have to only show how many hit we got in each hour. the table is very huge. output should look like this:

Row# |     Date    |  Time  | Hit Count
-----------------------------
1    | 07/05/2012  |  8:00  |  3
2    | 07/05/2012  |  9:00  |  4
3    | 07/05/2012  |  10:00 |  0
4    | 07/05/2012  |  11:00 |  5

My table is look like this:

"HitTime":

07/05/2012 08:02:24
07/05/2012 08:12:21
07/05/2012 08:23:00
07/05/2012 09:01:00
07/05/2012 09:08:14
07/05/2012 09:12:31
07/05/2012 09:22:27

..etc As you see in HitTime field I only have date and time, I need to show at same date, from for example 8:00 till 8:59 how many hit did I get, and it should be for all day, from very first second that day starts till very end second of the day.

possible duplicate of [How to group time by hour or by 10 minutes](http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — GSerg, May 16 '12 at 12:51

Quassnoi · Answer 1 · 2012-05-16T13:05:34.993

4

DECLARE @current_date DATETIME

SET @current_date = '2012-05-07';

WITH    hours (hr) AS
        (
        SELECT  0
        UNION ALL
        SELECT  hr + 1
        FROM    hours
        WHERE   hr < 23
        )
SELECT  ROW_NUMBER() OVER (ORDER BY hr) AS rn,
        @current_date AS [date],
        CONVERT(VARCHAR(5), DATEADD(hour, h.hr, @current_date), 108) AS [time],
        COUNT(hs.hittime) AS hitcount
FROM    hours h
LEFT JOIN
        hits hs
ON      hs.hittime >= DATEADD(hour, h.hr, @current_date)
        AND hs.hittime < DATEADD(hour, h.hr + 1, @current_date)
GROUP BY
        hr

edited May 16 '12 at 13:05

answered May 16 '12 at 12:52

Quassnoi

413,100
91
616
614

+1, but OP is using SQL Server 2005, which does not allow for a `DATE` type variable, and you can't declare and set it in the same statement. – KM. May 16 '12 at 12:55
@KM: seems AJAX poll time is too slow on SO :) – Quassnoi May 16 '12 at 12:56

score 0 · Answer 2 · answered May 16 '12 at 13:13

0

How about this?

;WITH aggregation(hit_date, hit_time)
AS
(
SELECT DATEADD(dd, DATEDIFF(dd, 0, hittime), 0)
     , DATEPART(hour, hittime)
FROM test
)
SELECT ROW_NUMBER() OVER (ORDER BY hit_date, hit_time) AS rn
     , CONVERT(VARCHAR,hit_date,101) as [date]
     , CAST(hit_time AS VARCHAR) + ':00' as [time]
     , COUNT(*) as hit_count
FROM aggregation
  GROUP BY hit_date
         , hit_time

answered May 16 '12 at 13:13

Mark M

976
5
14

Quite right. Missed the 10:00 row. And most of the text underneath the sample data. – Mark M May 16 '12 at 13:17

Manish · Answer 3 · 2012-05-18T09:42:36.543

0

WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], substring(convert(varchar,hit_time,108), 0, 4)+'00' as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]

And if You want AM/PM then :

WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], (substring(convert(varchar,hit_time,100), 12, 4)+'00'+substring(convert(varchar,hit_time,100),18,2)) as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]
GO

edited May 18 '12 at 09:42

answered May 17 '12 at 10:23

Manish

517
1
3
19

Thanks Manish but I guess the result is not correct, I mean the hit counters are not correct(its too much). 1) Please explain the query a little for me for example what is 101 ? 2) how could I show the time as PM/AM or I even could use 24 hour also. – Pouria May 17 '12 at 15:24
@Pouria I have edited my answer now you will get your desired output and show time in 24 hours formate, and talking about 101 or 108 in a query these are the datetime formate.http://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html might be this link is useful for you. – Manish May 18 '12 at 08:03

SQL how to sum events in one hour between two dates and show it in one row

3 Answers3

Linked