SQL как суммировать события за один час между двумя датами и показывать их в одной строке

Я разрабатываю отчет с C # и SQL Server 2005, Я должен только показать, сколько хитов мы получили за каждый час. стол очень огромный. вывод должен выглядеть так:

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

Моя таблица выглядит так:

"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

..так далее Как вы видите в поле HitTime, у меня есть только дата и время, мне нужно показывать в одну и ту же дату, например, с 8:00 до 8:59, сколько попаданий я получил, и это должно быть за весь день, с самой первой секунды этот день начинается до самого конца второго дня.

 GSerg16 мая 2012 г., 14:51
возможный дубликатHow to group time by hour or by 10 minutes

Ответы на вопрос(3)

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]

И если вы хотите AM / PM, то:

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
 Pouria17 мая 2012 г., 17:24
Спасибо, Маниш, но я думаю, что результат не правильный, я имею в виду, что счетчики попаданий не верны (это слишком). 1) Пожалуйста, объясните мне вопрос, например, что такое 101? 2) как я могу показать время в формате PM / AM или даже использовать 24 часа.
 Pouria22 мая 2012 г., 12:39
спасибо, наверное, сейчас работает
 18 мая 2012 г., 10:03
@Pouria Я отредактировал свой ответ, теперь вы получите желаемый результат и покажете время в формате 24 часа, и, говоря о 101 или 108 в запросе, это формат datetime.sqlhub.com/2009/04/list-of-all-available-datetime-format.html Может быть, эта ссылка полезна для вас.

Как насчет этого?

;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
 16 мая 2012 г., 15:15
Это не будет показывать нули.
 16 мая 2012 г., 15:17
Совершенно верно. Пропустил 10:00 подряд. И большая часть текста под образцом данных.
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
 16 мая 2012 г., 14:56
@KM: кажется, что время опроса AJAX слишком мало для SO :)
 16 мая 2012 г., 15:08
@KM: вы правы, пропустили тег 2005 года.
 16 мая 2012 г., 14:55
+1, но OP использует SQL Server 2005, который не позволяетDATE введите переменную, и вы не можете объявить и установить ее в одном выражении.

Ваш ответ на вопрос