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'
(向http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server用于收集他们的基本 sql)
我试图找到一小时内发生的事件的计数,因此结果集将如下所示:
2007-01-01 12:00 3
2007-01-02 5:00 2
2007-01-03 4:00 1
2007-01-07 3:00 1
我一直在玩 dateadd 和 round 和 group 但没有得到它。有人可以帮忙吗?
Thanks.
这个怎么样?假设 SQL Server 2008:
SELECT CAST(StartDate as date) AS ForDate,
DATEPART(hour,StartDate) AS OnHour,
COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
DATEPART(hour,StartDate)
2008 年之前:
SELECT DATEADD(day,datediff(day,0,StartDate),0) AS ForDate,
DATEPART(hour,StartDate) AS OnHour,
COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
DATEPART(hour,StartDate)
这导致:
ForDate | OnHour | Totals
-----------------------------------------
2011-08-09 00:00:00.000 12 3
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)