Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Group by time span

    I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.

    Here is the query I would like to change:
    SELECT count(*), clientTime
    FROM dbo.V_COMBINED
    WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value')
    GROUP BY clientTime

    It returns records like:
    1 | 2006-02-16 23:21:05.250
    1 | 2006-02-16 23:21:05.267
    1 | 2006-02-16 23:21:06.470

    I'd like it to return records like:
    5 | 2006-02-16 23:21:06 - 23:21:10
    3 | 2006-02-16 23:21:11 - 23:21:15
    4 | 2006-02-16 23:21:16 - 23:21:20

    Anyone know how I could do this? Is it even possible?

    Thanks
    -Doug Picanzi

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Yes It is possible.
    Post your table structure, sample data and expected result.
    -----------------
    KH


  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to do this, but I'd use:
    Code:
    SELECT count(*), DateAdd(second, -DatePart(second, clientTime) % 5
    ,  DateAdd(ms, -DatePart(ms, clientTime), clientTime))
       FROM dbo.V_COMBINED
       WHERE (sessionId = '122b')
          AND (type = N'sys_goodaction')
          AND (paraName = 'value')
       GROUP BY DateAdd(second, -DatePart(second, clientTime) % 5
    ,     DateAdd(ms, -DatePart(ms, clientTime), clientTime))
    -PatP

Posting Permissions

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