Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: time intervals - set operation

    While my first instinct is to use a loop, I'm forcing myself to try harder to use set operations whenever possible. However this (probably easy) problem is stumping me.

    I want to summarize data by blocks of time and I cant think of how to do it with a simple query (ie, no loop). Take a look at the sample table below. I want to summarize the counter for every 5 minute block of time.

    eventTime(datetime) counter(int)
    2010-08-23 00:02:55 4
    2010-08-23 00:04:12 1
    2010-08-23 00:04:48 2
    2010-08-23 00:06:02 8
    2010-08-23 00:06:19 11
    2010-08-23 00:07:32 3


    the result should be something like:

    bockOfTime(datetime) counterSum(int)
    2010-08-23 00:05:00 7
    2010-08-23 00:10:00 22

    Heres what I have so far

    select eventTime as [blockOfTime], sum(counter) as [counterSum] from TABLE group by five minute blocks please

    Which for some reason doesnt work

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Is this a homework assignment?

    I am amased at how many postings we've had over the last two weeks dealing with counting the number of occurances of an event over various blocks of time.

    It's like this month's Y2K issue?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Feb 2010
    Posts
    75
    LOL, I take it that means its an easy question and I should know the answer? I graduated UNI 6 years ago, thanks for making me feel like I need to go back haha.

  4. #4
    Join Date
    Feb 2010
    Posts
    75
    If it helps put your mind at ease, I'm basically still trying to solve my other question posted here, by breaking it down into managable chunks. Ive gotten this one part written in csharp and Id like to see how the speed compares to an SQL set operation version of it (if thats even possible)

    http://www.dbforums.com/microsoft-sq...l-c-sharp.html


    Honestly, I dont know how you can look at any of the posts on this entire forum and not wonder if its a homework question.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    BeerOclock, I am not at a place where I can actually test this but this might work ( or work with a little tweaking):
    Code:
    WITH DATE_RANGE(START_INTERVAL, END_INTERVAL)
      AS (SELECT '2010-08-23 00:00:00', '2010-08-23 00:04:59'
          UNION
          SELECT DATEADD(MI, 5,START_INTERVAL), DATEADD(MI, 5,END_INTERVAL)
          FROM DATE_RANGE
          WHERE END_INTERVAL < '2010-08-23 23:59:59'
         )
    SELECT START_INTERVAL, END_INTERVAL, SUM(COUNTER) AS COUNTERSUM
    FROM DATE_RANGE
           INNER JOIN
         table-name
           ON EventTime BETWEEN START_INTERVAL, END_INTERVAL
    GROUP BY START_INTERVAL, END_INTERVAL
    I am hard coding the start date range and only generating one days worth of Start / End ranges of 5 minutes each (from 00:00 to 4:59, 5:00 to 9:59, etc.). This can be modified to have more or less time as needed.

    Then I am joining the CTE table DATE_RANGE with your table when the date/time is between the Start and End Intervals. When you Sum / Group you should get the values you are looking for.

    PS if you need to show all 5 minute ranges even if there is no data, a Left Outer Join can be used.

  6. #6
    Join Date
    Feb 2010
    Posts
    75
    I dont think they taught us that technique in class... lol j/k

    I'm not really sure I grasp what your code is doing, or if I could extend it to work for say, 1000 5-minute intervals without using a loop. I'll check it out tomorro and get back, thanks!

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Here is a cleaned up version (that mostly works). The only problem it has is the number of levels the recursion allows. I don't have time to look up how to get around the default limit of 100 but I am sure there is a way.
    Code:
    WITH DATA_TAB (EVENTTIME, COUNTER)
      AS (SELECT CAST('2010-08-23 00:02:55' AS DATETIME),  4 UNION ALL
          SELECT CAST('2010-08-23 00:04:12' AS DATETIME),  1 UNION ALL
          SELECT CAST('2010-08-23 00:04:48' AS DATETIME),  2 UNION ALL
          SELECT CAST('2010-08-23 00:06:02' AS DATETIME),  8 UNION ALL
          SELECT CAST('2010-08-23 00:06:19' AS DATETIME), 11 UNION ALL
          SELECT CAST('2010-08-23 00:07:32' AS DATETIME),  3
         )
       , DATE_RANGE (START_INTERVAL, END_INTERVAL, LEVELS)
      AS (SELECT CAST('2010-08-23 00:00:00' AS DATETIME)
               , CAST('2010-08-23 00:04:59' AS DATETIME)
               , 1
          UNION ALL
          SELECT DATEADD(mi, 5, START_INTERVAL)
               , DATEADD(mi, 5, END_INTERVAL)
               , LEVELS + 1
          FROM DATE_RANGE
          WHERE START_INTERVAL < CAST('2010-08-23 23:59:59' AS DATETIME)
            AND LEVELS < 100
         )
    SELECT START_INTERVAL, END_INTERVAL, SUM(COUNTER) AS COUNTERSUM
    FROM DATE_RANGE
           INNER JOIN
         DATA_TAB
           ON EVENTTIME BETWEEN START_INTERVAL AND END_INTERVAL
    GROUP BY START_INTERVAL, END_INTERVAL
     
    START_INTERVAL          END_INTERVAL             COUNTERSUM
    ----------------------- ----------------------- -----------
    2010-08-23 00:00:00.000 2010-08-23 00:04:59.000           7
    2010-08-23 00:05:00.000 2010-08-23 00:09:59.000          22
    The first part (starting with WITH) is the Common Table Expression (CTE). It allows the result sets of queries to be used later on in the same query. It is a variation of a derived table.

    The first CTE just replicates you data to a derived table DATA_TAB.
    The second CTE uses recursion to generate all the 5 minute start / end intervals into another derived table DATE_RANGE.
    The first query sets the base starting value(s). Then a UNION ALL us just to both add to DATE_RANGE and read from DATE_RANGE. It adds 5 minutes to the START / END intervals each time it finds another row. I originally limited the recursion to stop after 24 hours but this generated more than the 100 row recursion default limit. To get the query to complete, I added LEVELS, added 1 to it each time and stop the recursion after 100 levels have been reached.

    Once you have the derived table(s), I join them with a between. This basically 'appends' the Start / End interval values to your data rows if you date time value is between them.

    Finally, these Start / End intervals are Grouped to Sum the Counter values to the the range totals.

    Now if I can just find that Recursion Level control and how to implement it.

    Once that has been resolved, you can limit the recursion to stop after however many values you need (instead of 24 hours).

    PS If you already have a table of 5 minute date ranges, you can eliminate the CTE and just join that table with your data table. It will probably run faster (since you are not generation the list of ranges each time).

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I found the syntax to allow more recursion. Here is an updated version with the OPTION keyword (and a few corrections). It will handle 4 days (2010-08-23 through 2010-08-27. If you want more, you will need to increase the MAXRECURSION to a larger number.
    Code:
    WITH DATA_TAB (EVENTTIME, COUNTER)
      AS (SELECT CAST('2010-08-23 00:02:55' AS DATETIME),  4 UNION ALL
          SELECT CAST('2010-08-23 00:04:12' AS DATETIME),  1 UNION ALL
          SELECT CAST('2010-08-23 00:04:48' AS DATETIME),  2 UNION ALL
          SELECT CAST('2010-08-23 00:06:02' AS DATETIME),  8 UNION ALL
          SELECT CAST('2010-08-23 00:06:19' AS DATETIME), 11 UNION ALL
          SELECT CAST('2010-08-23 00:07:32' AS DATETIME),  3
         )
       , DATE_RANGE (START_INTERVAL, END_INTERVAL
      AS (SELECT CAST('2010-08-23 00:00:00' AS DATETIME)
               , CAST('2010-08-23 00:04:59' AS DATETIME)
          UNION ALL
          SELECT DATEADD(mi, 5, START_INTERVAL)
               , DATEADD(mi, 5, END_INTERVAL)
          FROM DATE_RANGE
          WHERE END_INTERVAL < CAST('2010-08-27 23:59:59' AS DATETIME)
         )
    SELECT START_INTERVAL, END_INTERVAL, SUM(COUNTER) AS COUNTERSUM
    FROM DATE_RANGE
           INNER JOIN
         DATA_TAB
           ON EVENTTIME BETWEEN START_INTERVAL AND END_INTERVAL
    GROUP BY START_INTERVAL, END_INTERVAL
    OPTION (MAXRECURSION 1500)

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       eventtime			DATETIME
    ,  [counter]			INT
    )
    
    INSERT INTO @t
          SELECT '2010-08-23 00:02:55', 4
    UNION SELECT '2010-08-23 00:04:12', 1
    UNION SELECT '2010-08-23 00:04:48', 2
    UNION SELECT '2010-08-23 00:06:02', 8
    UNION SELECT '2010-08-23 00:06:19', 11
    UNION SELECT '2010-08-23 00:07:32', 3
    
    SELECT Min(eventtime), Sum([counter])
       FROM @t
       GROUP BY DateAdd(day, DateDiff(day, 0, eventtime), 0)
    ,     DateDiff(Minute, DateAdd(day, DateDiff(day, 0, eventtime)
    ,     0), eventtime) / 5
    No limits that I know of, but keep me honest.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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