Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256

    Unanswered: Time Slicing a table

    I need to generate a report of peak network usage per hour over a six month period. I have a table which contains (among other things) an int id, and start and stop times for each network connection.

    Right now, I am running something like this:

    DECLARE @start_time DATETIME, @end_time DATETIME, @slice_time DATETIME

    SET @start_time=CAST('1/1/2003' AS DATETIME)
    SET @end_time=CAST('7/1/2003' AS DATETIME)

    SET @slice_time=@start_time
    WHILE @slice_time<@end_time
    BEGIN
    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)
    END

    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?

    TIA
    -bpd

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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)
    From SequentialValueTable
    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.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •