시간당 DateTime 수에 따른 SQL Server 그룹?

시간당 DateTime 수에 따른 SQL Server 그룹?

---

    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 에 기본 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 및 grouping을 가지고 놀았지만 얻지 못했습니다. 누구든지 도울 수 있습니까?

감사.

---

이것은 어떤가요? 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

---

또는 GROUP BY시간과 요일 만 :

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

산출:

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

---

나는 이것을 다른 곳에서 찾았습니다. 나는이 대답을 좋아한다!

SELECT [Hourly], COUNT(*) as [Count]
  FROM 
 (SELECT dateadd(hh, datediff(hh, '20010101', [date_created]), '20010101') as [Hourly]
    FROM table) idat
 GROUP BY [Hourly]

---

동일한 열과 적절한 날짜 시간 형식에 날짜와 시간이 있고 날짜 시간별로 정렬 된 다음 SQL을 사용하여이를 수행 할 수도 있습니다.

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

ReferenceURL : https://stackoverflow.com/questions/7001718/sql-server-group-by-count-of-datetime-per-hour