CODEWITHSUNDEEP
sql-server
curious1
curious1

Reputation: 14727

Create a histogram based on record creation time

My system uses SQL Server 2014. It has a table called Report. I would like to know at what time most reports were created. The table has a field called crateTime, which records the time of record creation. I have the following statement

select dateadd(minute, datediff(minute, 0, r.createTime), 0), count(*) from Report r
group by dateadd(minute, datediff(minute, 0, r.createTime), 0)

This statement can produce the count of records at each minute. However, I want to get the count based on the interval of five minutes. How can I do that?

Upvotes: 0

Views: 49

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

Try:

SELECT DATEADD(minute, DATEDIFF(minute, '', r.createTime)/5*5, ''), count(*)
FROM Report r
GROUP BY DATEADD(minute, DATEDIFF(minute, '', r.createTime)/5*5, ''), 0);

Note that the default datetime value is used when an empty string instead of zero is specified so this technique will work with other temporal types as well.

Upvotes: 2

Related Questions