Hi All,

I am new in SQL Server and creating some SQL script that should give total transaction count per hour for a day. I need simply hour and count result. My table contains DateCreated column and unique ID column.

Please help. Thanks in advance.

Hi,

Please try below sql query;

SELECT DATEPART(hh, DateCreated) AS Hour, count(ID) IDCount
FROM [dbo].[YourTableName]
WHERE DateCreated >= '2015-09-07'
GROUP BY DATEPART(hh, DateCreated)

Slight modification of the suggested solution:

SELECT cast(DateCreated as DATE) as [Date], DATEPART(hour, DateCreated) AS [Hour], 
count(ID) as Transactions
FROM [dbo].[YourTableName]

GROUP BY CAST(DateCreated as DATE), DATEPART(hour, DateCreated)

Thanks all,

I am able to see result now. But need some more clear. Can I get result as expected

Try

;with cte as (SELECT cast(DateCreated as DATE) as [Date], 
dateadd(hour,DATEPART(hour, DateCreated),'19000101') AS [HourOnly], 
count(ID) as Transactions
FROM [dbo].[YourTableName]

GROUP BY CAST(DateCreated as DATE), DATEPART(hour, DateCreated))

select [Date], REPLACE(convert(varchar(20),HourOnly, 100),7),':00','')+'-' + 
REPLACE(convert(varchar(20),dateadd(hour,1,HourOnly), 100),7),':00',''), Transactions
from cte