SQL Group by Minute- Expanded
- by Barnie
I am working on something similar to this post here:
TS SQL - group by minute
However mine is pulling from an message queue, and I need to see an accurate count of the amount of traffic the Message Queue is creating/ sending, and at what time
Select * From MessageQueue mq
My expanded version of this though is the following:
A) User defines a start time and an end time (Easy enough using Declare's @StartTime and @EndTime
B) Give the user the option of choosing the "grouping". Will it be broken out by 1 minutes, 5 minutes, 15 minutes, or 30 minutes (Max). (I had thought to do this with a CASE statement, but my test problems fall apart on me.)
C) Display the data to accurately show a count of what happened during the interval (Grouping) selected.
This is where I am at so far
SQL Blob:
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT DATEPART(n, mq.cre_date)/5 as Time --Trying to just sort by 5 minute intervals
,CONVERT(VARCHAR(10),mq.Cre_Date,101)
,COUNT(*) as results
FROM dbo.MessageQueue mq
WHERE mq.cre_date BETWEEN @StartDate AND @EndDate
GROUP BY DATEPART(n, mq.cre_date)/5 --Trying to just sort by 5 minute intervals
, eq.Cre_Date
This is the output I would like to achieve:
[Time] [Date] [Message Count]
1300 06/26/2012 5
1305 06/26/2012 1
1310 06/26/2012 100
