SQL Server Query to select a count group by time period

mnowg1ta 于 2023-11-16 发布在其他

关注(0)|答案(4)|浏览(134)

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 :)

sql-server

来源：https://stackoverflow.com/questions/35585575/query-to-select-a-count-group-by-time-period

4条答案

按热度按时间

sirbozc51#

DATEPART(MINUTE,...) are just the minutes within one hour, not the minutes within one day. Consider the hours as well

select (60 * DATEPART(HOUR, usr.STARTDATETIME) + 
        DATEPART(MINUTE, usr.STARTDATETIME)) % 30, COUNT(*)
...

赞(0）回复(0）举报 2023-11-16

7jmck4yq2#

This will group by on the hour (not 30 min)

declare @startdatetime datetime = '2016-02-03 00:00:00'
declare @enddatetime datetime = '2016-02-19 23:59:59'
declare @apiserviceid int = 21

select min(usr.STARTDATETIME), max(usr.STARTDATETIME), count(0)
from TABLE 1 usr
where usr.APIREQUESTID = @apiserviceid
and usr.STARTDATETIME >= @startdatetime and usr.STARTDATETIME <= @enddatetime
group by convert(char(13),usr.STARTDATETIME,121)
order by 1;

赞(0）回复(0）举报 2023-11-16

juud5qan3#

select cast(year(usr.STARTDATETIME) as varchar)+''
+cast(month(usr.STARTDATETIME) as varchar)+
'-'+cast(day(usr.STARTDATETIME) as varchar)+' '+CAST(DATEPART(HOUR, usr.STARTDATETIME) AS VARCHAR)+':'+
CASE WHEN DATEPART(MINUTE, usr.STARTDATETIME)>30 THEN '31 - '+ 
CAST((DATEPART(HOUR, usr.STARTDATETIME)+1) AS VARCHAR) +':00' ELSE '00 - 
'+CAST(DATEPART(HOUR, usr.STARTDATETIME) AS VARCHAR)+':30' END , COUNT(*)
from TABLE 1 usr
where usr.APIREQUESTID = @apiserviceid
and usr.STARTDATETIME >= @startdatetime and usr.STARTDATETIME <= @enddatetime
group by 
cast(year(usr.STARTDATETIME) as varchar)+'-'+
cast(month(usr.STARTDATETIME) as varchar)+'-'+cast(day(usr.STARTDATETIME) as     
varchar)+' '+CAST(DATEPART(HOUR, usr.STARTDATETIME) AS VARCHAR)+':'+
CASE WHEN DATEPART(MINUTE, usr.STARTDATETIME)>30 THEN '31 - '+ 
CAST((DATEPART(HOUR, usr.STARTDATETIME)+1) AS VARCHAR) +':00' ELSE '00 - 
'+CAST(DATEPART(HOUR, usr.STARTDATETIME) AS VARCHAR)+':30' END 
order by 1;

赞(0）回复(0）举报 2023-11-16

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.

DECLARE @startdate DATETIME = '2016-02-23'--your_starting_date
DECLARE @enddate DATETIME = '2016-03-01' --your_ending_date

;WITH cte AS (

SELECT 

@startdate AS start_time
, DATEADD(MINUTE, 30, @startdate) AS end_time

UNION ALL 

SELECT DATEADD(MINUTE, 30, start_time) AS start_time
, DATEADD(MINUTE, 30, end_time) AS end_TIME 
FROM cte
WHERE end_time <= @enddate
)

SELECT * 
INTO #time_table
FROM CTE

OPTION (MAXRECURSION 32727)
GO 

SELECT 
start_time
, end_time
, SUM(CASE WHEN your_time_column BETWEEN start_time AND end_time THEN 1 ELSE 0 END) AS total_count

FROM #time_table 
INNER JOIN your_table  --left join if you want all time slots with 0 occurrences
ON your_time_column BETWEEN start_time AND end_time

GROUP BY
start_time
, end_time

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Query to select a count group by time period

4条答案

相关问题

热门标签

最新问答