#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    10

    Querying for time spans when record count exceed X


    I'm having trouble wrapping my head around a query. I have a table of call records with timestamps. I need to know if the number of records exceed X within any given 5 minute time frame.

    I've written the following query, that will group records by time strings consisting of running floor functions on dateparts of the call_date:
    Code:
    select 
    right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2) as date, right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2) as time,
    count(id) 
    from cdr_nextone_temp 
    where call_date >= DateAdd(hh, -12, (select max(call_date) from cdr_nextone_temp)) 
    group by right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2), right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2) 
    order by right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2), right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2)
    Which results in the following example output:
    Code:
    date       time       count
    01-06     12:00     16
    01-06     12:05     18
    01-06     12:10     13
    01-06     12:15     18
    01-06     12:20     27
    01-06     12:25     22
    01-06     12:30     35
    01-06     12:35     28
    01-06     12:40     44
    01-06     12:45     44
    01-06     12:50     55
    01-06     12:55     176
    01-06     13:00     622
    01-06     13:05     340
    As you might see, the problem is that it only counts calls between set, formulaic time frames. I need it to check if calls exceed a certain amount within ANY time frame.

    For example, let's suppose we're looking for 5-minute time frames that exceed 100 calls:

    If 30 calls took place between 12:03 and 12:04 and another 75 calls took place between 12:06 and 12:07 and no other calls besides, the above query would not catch it. The first 30 calls would be grouped as 12:00 and the next 75 calls would be grouped as 12:05. However, 105 calls took place between 12:03 and 12:07, all within a 5-minute time frame.

    I can't seem to think of an elegant approach to this problem. A rugged approach would be to start at min(call_date) and check the next 5-minutes. Then add 1 second to min(call_date) and check the next 5 minutes.... then add 2 seconds... iterating over these steps until we reach max(call_date).

    Any ideas?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Using a variable table works.

    Code:
    DECLARE @work TABLE (d varchar(20), t varchar(20), ct int)
    
    INSERT INTO @work 
    select 
    right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2) as date, right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2) as time,
    count(id) 
    from cdr_nextone_temp 
    where call_date >= DateAdd(hh, -12, (select max(call_date) from cdr_nextone_temp)) 
    group by right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2), right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2) 
    order by right('00' + convert(varchar,month(call_date)),2) + '-' + right('00' + convert(varchar,day(call_date)),2), right('00' + convert(varchar,datepart(hh, call_date)),2) + ':' + right('00' + convert(varchar,floor(datepart(mi, call_date)/5)*5),2)
    
    SELECT
    d + ' ' + t as 'Date Time',
    ct as 'Count'
    FROM @work
    WHERE ct > X
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    10
    Thanks Vomster, but that doesn't quite do the trick. What you did is the same as adding the following clause to the GROUP BY of the original query:

    Code:
    HAVING count(id) > X
    But it still wouldn't return 12:03 - 12:08 as a row, using the example from my original post.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Show what you want the output to look like.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    10
    The output would look the same except it wouldn't be limited to exact 5-minute increments. A possible output would be:

    Code:
    date       time       count
    01-06     12:01     16
    01-06     13:52     18
    01-06     15:06     13
    01-06     15:15     18
    01-06     20:13     27
    01-06     22:21     22
    Each datetime would represent the number of records with that time stamp plus records with time stamps within the next 5 minutes.

IMN logo majestic logo threadwatch logo seochat tools logo