SET @start_time=CAST('1/1/2003' AS DATETIME)
SET @end_time=CAST('7/1/2003' AS DATETIME)
INSERT INTO tbl_utilization
SELECT @slice_time, COUNT(id) FROM tbl_metered_network_connections WHERE @slice_time BETWEEN start_time AND stop_time
@slice_time = DATEADD(minute,5,@slice_time)
start_time and end_time are actually sp params, and I am getting more data than this. The result would be a table of simultaneous network connections in 5-minute intervals, from which I could easily use a MAX to get the peak during any given interval. (I doubtless will have future requirements, which is why I am holding the data in a table.)
Is there a better technique for getting the peak usage?
I haven't tried your accumulator method, so I don't know how it would compare in speed to a method I've used in the past that relies on a table of sequential values.
Create a table of sequential values starting with zero. If will need to have at least as many values as tha maximum number of slices you'll be checking at at time.
Create a temporary table (or table variable) for storing TimeSlice values and populate it with:
insert into @TimeSliceTable
Select dateadd(mm, SequentialValue * 5, @StartDate)
Where dateadd(mm, SequentialValue * 5, @StartDate) <= @EndDate
(I think mm is for minutes. You'll need to double check...)
Now join this to your data table:
Select TimeSliceTable.TimeSlice, count(*)
from @TimeSLiceTable TimeSliceTable, tbl_metered_network_connections
where TimeSlice between start_time AND stop_time
...notice that the query datasources are not joined.
This can also be run as a single statement, without using a temporary table for storing TimeSlice values, and I also have a function (at work, not here at home) that will return a table of sequential values on the fly so you do not need to have one resident in the database.
I put a table of sequential values in almost all my databases, because sooner or later I come across a query that can be greatly simplified by using it.