I have a typical log table with many details and a datetime of when the record was created.
I'm trying to analyse how many times does an event occurs per a time period (each 30 mins). I need to be able to analyze for a period bigger than one day. Basically, my desired output would be something like:
Period | Total
Day 1 00:00 - 00:30 | 23
Day 1 00:30 - 01:00 | 0
Day 1 01:00 - 01:30 | 534
...
Day 2 23:00 - 23:30 | 23
I'm flexible on the date column layout - I just need to be "readable"!
Here's my try but it's not working very well
declare @startdatetime datetime = '2016-02-03 00:00:00'
declare @enddatetime datetime = '2016-02-19 23:59:59'
declare @apiserviceid int = 21
select DATEPART(MINUTE, usr.STARTDATETIME) % 30, COUNT(*)
from TABLE 1 usr
where usr.APIREQUESTID = @apiserviceid
and usr.STARTDATETIME >= @startdatetime and usr.STARTDATETIME <= @enddatetime
group by
(DATEPART(MINUTE, usr.STARTDATETIME) % 30)
order by 1;
Thanks :)
4条答案
按热度按时间sirbozc51#
DATEPART(MINUTE,...)
are just the minutes within one hour, not the minutes within one day. Consider the hours as well7jmck4yq2#
This will group by on the hour (not 30 min)
juud5qan3#
kninwzqo4#
I would recommend using a recursive CTE to generate all of your 30 min time segments and then JOIN on your table to find the occurrences.