select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
sum(SnapShotValue)
from YourTable
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)
SE-Data
datediff(minute, 0, CaptureTime)
gives you the number of minutes since 1900-01-01T00:00:00
.
dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)
adds the number of minutes since 1900-01-01T00:00:00
to 1900-01-01T00:00:00
ending up with a datetime with only minutes.
The 1+
is there because you wanted the next minute.
To do the same with a 5 minute interval you need to do some calculations. Divide the minutes with 5
and multiply with 5
gives you the minutes rounded down to a 5 minute precision. This works because the result of an integer division in SQL Server is an integer.
dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- replace WITH (nolock)
SELECT [Time]
,AVG(NULLIF(Duration,0)) AS [Mean Response Time]
,AVG(CASE WHEN Rn in (Round(CN/2.0,0),Round((CN+1)/2.0,0))
THEN Duration END) [Median]
FROM
(
SELECT [Time], Duration,
Rn = Row_Number() over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
Time
order by Duration),
Cn = Count(1) over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
Time)
FROM (
SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time],
Duration
FROM [Application].[Exchange]
WHERE [Started] >= '20121024' AND [Started] < '20121025'
) X
) Y
GROUP BY [Time]
ORDER BY [Time];
Best Answer
First, a table and some sample data to play with:
(I'd do this in sqlfiddle but I'm not sure it supports
GO <int>
to have many rows, and it chokes onINSERT
> 8000 characters.)Now a stored procedure:
And some sample usage:
For brevity, I'll show the results for the last call, but you can play with the others.
Some notes:
dates
to produce all the intervals for the day, rather than all the intervals between the min and max time found indbo.SomeTable
on that day. The following variation takes care of this, by presenting the data for a single day starting from midnight and incrementing by @MinutInterval:...
Sample calls:
Abridged results from the last call:
(Again if you don't want to include the intervals with no counts, change the left outer join to an inner join. You also might find some odd results if you choose an interval that doesn't fit nicely inside of 1440. I'll leave that case as an exercise to the reader.)