SQL : counting calls in 10 min increments

Question

I need to determine how many calls we take in 10 min increaments for the month. I have the following SQL query that works on the half hour but I can not get it to work on 10 minute increments. I am probably missing something simple.. Any help would be appreciated. Thanks

select 
    count(*) as Callcount,
    case 
       when datepart(mi, PBXqtime) < 30 
          then dateadd(hh, datediff(hh, 0, PBXqtime) + 0, 0)
          else dateadd(mi, 30, dateadd(hh, datediff(hh, 0, PBXqtime) + 0, 0)) 
    end as time
from 
    callcomplete 
where 
    CustId = '3100472' 
    and PBXQTime between '11/1/11 0:0:00' and '11/30/11 23:59:00'
group by 
    case 
       when datepart(mi, PBXqtime) < 30 
        then dateadd(hh, datediff(hh, 0, PBXqtime) + 0, 0) 
        else dateadd(mi, 30, dateadd(hh, datediff(hh, 0, PBXqtime) + 0, 0)) 
    end

Wouldn't you just change the numeric 30's to 10's? Why doesn't that work? — RBarryYoung, Sep 11 '12 at 14:21
@RBarryYoung: because you'd need 5 WHEN clauses and an ELSE clause in each of the CASE statements. — Jonathan Leffler, Sep 11 '12 at 14:22
SQL is a language, implemented differently by different database products. One of the largest areas of variation is in the datetime manipulation functions. I could guess that this is SQL Server (or possibly Sybase), but please edit your question and add a suitable tag. — Damien_The_Unbeliever, Sep 11 '12 at 14:50

Kermit · Answer 1 · 2012-09-11T14:53:09.933

1

Haven't tested, but:

SELECT
    DATEPART(DAY, pbxqtime) AS [day], DATEPART(HOUR, pbxqtime) AS [hour], 
    (DATEPART(MINUTE, pbxqtime) / 10) AS [interval], COUNT(*) AS Callcount
FROM
    callcomplete
WHERE
    custid = '3100472'
    AND pbxqtime BETWEEN '11/1/11 0:0:00' AND '11/30/11 23:59:00'
GROUP BY
    DATEPART(DAY, pbxqtime), DATEPART(HOUR, pbxqtime), 
    (DATEPART(MINUTE, pbxqtime) / 10)

This is assuming a MSSQL environment. In addition, there is a heated debate raging below with regards to how you should specify your date range. It is being argued that using simple operators such as >= and <= work better and are cleaner. Therefore, you can alternatively use AND pbxqtime >= '2011-11-01' AND pbxqtime < '2011-12-01'.

edited Sep 11 '12 at 14:53

answered Sep 11 '12 at 14:27

Kermit

33,827
13
85
121

2

**this is a lame line of code: `AND pbxqtime BETWEEN '11/1/11 0:0:00' AND '11/30/11 23:59:00'`, it makes me crazy to see things coded this way!** you will miss any data in the last minute of the day! Don't give me some BS about that never happening, I've debugged this same line of code in a legacy app many times before because data was missing. The best way to code this is to use less than the first day of the next month, like: `AND pbxqtime>='2011-11-1' AND pbxqtime<'2011-12-1'` do it this way, every time and you will never have a problem. – KM. Sep 11 '12 at 14:33
@KM Thanks for your comment. Wouldn't `BETWEEN '2011-11-01' AND '2011-11-30'` work as well? – Kermit Sep 11 '12 at 14:35
**NO IT WON'T.** just forget ever using `between`, I never code `between` > >= < <= work better and are more clear. `BETWEEN '2011-11-01' AND '2011-11-30'` translates to `BETWEEN '2011-11-01 00:00:00' AND '2011-11-30 00:00:00'`, so you miss just about the entire last day's data that way. – KM. Sep 11 '12 at 14:38
@KM Could you please look at [this fiddle](http://sqlfiddle.com/#!3/d6920/2)? It appears the behavior is the same. – Kermit Sep 11 '12 at 14:47
@njk: Of course `stamp BETWEEN '2012-09-10' AND '2012-09-11'` is the same (in your fiddle) as `stamp >= '2012-09-10' AND stamp <= '2012-09-11'`. The point is, both are wrong! What you need is `stamp >= '2012-09-10' AND stamp < '2012-09-12'`. – ruakh Sep 11 '12 at 14:52
your second query is wrong, it should be `SELECT * FROM dt WHERE stamp >= '2012-09-10' AND stamp < '2012-09-11'`. notice that the pattern is `AND ZZZ>=STARTDATE AND ZZZ=` and `<=` – KM. Sep 11 '12 at 14:54
Even aside from the other arguments (and I agree that inclusive start and exclusive end is the easiest to reason about), please try to avoid *ambiguous* date formats. Depending on settings, `'2012-09-10'` (for example) might be the 9th of October of the 10th of September. It's safer to omit the dashes - `'20120910'` will unambiguously be interpreted as 10th September. – Damien_The_Unbeliever Sep 11 '12 at 17:37
@Damien_The_Unbeliever, I am unaware of any format where the year is first and was followed by anything other than the month and then the day – KM. Sep 11 '12 at 19:39
1

@KM - I'm unaware of anyone sane who would use such a format. [And yet...](http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=290971) – Damien_The_Unbeliever Sep 11 '12 at 20:09
@KM - And, of course, I'm still advocating year, month, day. The issue is that SQL Server imposes different interpretations on such strings if there are separators. – Damien_The_Unbeliever Sep 11 '12 at 20:15
1

I've never seen an end user enter `20120910` but they always enter `2012-09-10` where I work. – KM. Sep 12 '12 at 12:30
Thanks everyone for your input.......I appreciate the help. Njk, your code got me a lot closer. And last but not least, I am an old programmer trying to get more experience with SQL....came from the old mainframe world....Burroughs to be exact....Thanks for the tips on the dates...and yes...I am missing the last 10 minutes of the day. Thanks again everyone! This dog learned a couple of new tricks today! – user1663080 Sep 17 '12 at 23:00

score 1 · Answer 2 · answered Sep 11 '12 at 14:54

I think that this will work (but untested since I don't have your data):

select 
    count(*) as Callcount,
    DATEADD(minute,(DATEDIFF(minute,'20100101',PBXQTime) / 10)*10,'20100101')
from 
    callcomplete 
where 
    CustId = '3100472' 
    and PBXQTime >= '20111101'
    and PBXQTime < '20111201'
group by 
    DATEADD(minute,(DATEDIFF(minute,'20100101',PBXQTime) / 10)*10,'20100101')

Just take the number of minutes since some fixed point in time, divide by 10 (which will round down to nearest integer), then multiply by 10 and add back to the same fixed point in time.

SQL : counting calls in 10 min increments

2 Answers2