create table #Events
(
EventID int identity primary key,
StartDate datetime not null,
EndDate datetime not null
)
go
insert into #Events (StartDate, EndDate)
select '2007-01-01 12:44:12 AM', '2007-01-01 12:45:34 AM' union all
select '2007-01-01 12:45:12 AM', '2007-01-01 12:46:34 AM' union all
select '2007-01-01 12:46:12 AM', '2007-01-01 12:47:34 AM' union all
select '2007-01-02 5:01:08 AM', '2007-01-02 5:05:37 AM' union all
select '2007-01-02 5:50:08 AM', '2007-01-02 5:55:59 AM' union all
select '2007-01-03 4:34:12 AM', '2007-01-03 4:55:18 AM' union all
select '2007-01-07 3:12:23 AM', '2007-01-07 3:52:25 AM'
(with apologies to http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server for harvesting their base sql)
I am trying to find the count of Events that occurred in an hour, so the result set would look like this:
2007-01-01 12:00 3
2007-01-02 5:00 2
2007-01-03 4:00 1
2007-01-07 3:00 1
I have been playing with dateadd and round and grouping but not getting it. Can anyone help?
Thanks.
This question is related to
sql-server
datetime
rounding
Alternatively, just GROUP BY the hour and day:
SELECT CAST(Startdate as DATE) as 'StartDate',
CAST(DATEPART(Hour, StartDate) as varchar) + ':00' as 'Hour',
COUNT(*) as 'Ct'
FROM #Events
GROUP BY CAST(Startdate as DATE), DATEPART(Hour, StartDate)
ORDER BY CAST(Startdate as DATE) ASC
output:
StartDate Hour Ct
2007-01-01 0:00 3
2007-01-02 5:00 2
2007-01-03 4:00 1
2007-01-07 3:00 1
I found this somewhere else. I like this answer!
SELECT [Hourly], COUNT(*) as [Count]
FROM
(SELECT dateadd(hh, datediff(hh, '20010101', [date_created]), '20010101') as [Hourly]
FROM table) idat
GROUP BY [Hourly]
You can also achieve this by using following SQL with date and hour in same columns and proper date time format and ordered by date time
SELECT dateadd(hour, datediff(hour, 0, StartDate), 0) as 'ForDate',
COUNT(*) as 'Count'
FROM #Events
GROUP BY dateadd(hour, datediff(hour, 0, LogTime), 0)
ORDER BY ForDate
Source: Stackoverflow.com