How to get count of average calls every 5 minutes using datetime sql

Question

I am running the following query

select DateTime
from Calls
where DateTime > '17 Oct 2018 00:00:00.000' and
      DialedNumberID = '1234'

What would this give me is a list of all the times that this number was dialled on the specific date.

pic here:

Essentially what I am looking for is a query that would give me the average calls that take place every X minutes and would like to run the query for the whole year.

Thanks

And what would the result look like? One single number representing the average amount of calls made within X minutes? — Thorsten Kettner, Oct 19 '18 at 10:38
And where are you stuck? Which problem are you facing you need an answer for from us? — Thorsten Kettner, Oct 19 '18 at 10:39
select count(*) / timestampdiff(minute, date '2018-01-01', now()) * 5 as avg_calls from calls where `DateTime` >= date '2018-01-01' and DialedNumberID = 1234; — ivan, Oct 19 '18 at 11:11
@SalmanA your query worked just need the average number of calls from your second colum ( a single number) — ivan, Oct 19 '18 at 11:33

score 0 · Answer 1 · answered Oct 19 '18 at 10:49

I guess you have a table named Calls with the columns DateTime and DialedNumberID.

You can summarize the information in that table year-by-year using the kind of pattern.

  SELECT YEAR(`DateTime`),
         DialedNumberID,
         COUNT(*) call_count
    FROM Calls
   GROUP BY YEAR(`DateTime`), DialedNumberID

The trick in this pattern is to GROUP BY f(date) . The function f() reduces any date to the year in which it occures.

Summary by five minute intervals, you need f(date) that reduces datestamps to five minute intervals. That function is a good deal more complex than YEAR().

DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') + INTERVAL (MINUTE(datestamp) - MINUTE(datestamp) MOD 5)

Given, for example, 2001-09-11 08:43:00, this gives back 2001-09-11 08:40:00.

So, here's your summary by five minute intervals.

  SELECT DATE_FORMAT(`DateTime`,'%Y-%m-%d %H:00')+INTERVAL(MINUTE(`DateTime`)-MINUTE(datestamp) MOD 5) interval_beginning,
         DialedNumberID,
         COUNT(*) call_count
    FROM Calls
   GROUP BY DATE_FORMAT(`DateTime`,'%Y-%m-%d %H:00')+INTERVAL(MINUTE(`DateTime`)-MINUTE(datestamp) MOD 5),
           DialedNumberID

You can make this query clearer and less repetitive by defining a stored function for that ugly DATE_FORMAT() expression. But that's a topic for another day.

Finally, append

  WHERE YEAR(`DateTime`) = YEAR(NOW())
    AND DialedNumberID = '1234'

to the query to filter by the current year and a particular id.

This query will need work to make it as efficient as possible. That too is a topic for another day.

Pro tip: DATETIME is a reserved word in MySQL. Column names are generally case-insensitive. Avoid naming your columns, in this case DateTime, the same as a reserved word.

Thorsten Kettner · Answer 2 · 2018-10-19T11:26:22.437

0

The average amount of calls per interval is the number of calls (COUNT(*)) divided by the minutes between the start and end of of the monitored period (TIMESTAMPDIFF(minute, period_start, period_end)) multiplied with the number of minutes in the desired interval (five in your example).

For MySQL:

select count(*) / timestampdiff(minute, date '2018-01-01', now()) * 5 as avg_calls
from calls
where `datetime` >= date '2018-01-01'
and dialednumberid = 1234;

For SQL Server:

select count(*) * 1.0 / datediff(minute, '20180101', getdate()) * 5 as avg_calls
from calls
where [datetime] >= '20180101'
and dialednumberid = 1234;

edited Oct 19 '18 at 11:26

answered Oct 19 '18 at 10:49

Thorsten Kettner

89,309
7
49
73

apaologies tagged this as mysql when I as looking for answers for sql server ... so this query doesnt seem to work – ivan Oct 19 '18 at 10:57
For SQL Server the function is called `DATEDIFF`, `NOW` is `GETDATE`, ANSI date literals are not supported, names are quoted with brackets and division of integers supresses the decimals. – Thorsten Kettner Oct 19 '18 at 11:24
how can also get max number of calls per 5 min interval – ivan Oct 19 '18 at 12:14
This is a bit difficult. For instance if I look at 1:00-1:05, 1:05-1:10, ... I may get a maximum of five calls, but if I look at 1:01-1:06, 1:06-1:11, ... I may suddenly get a maximum of ten calls or only two calls. So how do you want to define the five minute interval? Probably look at each record/time and count the recods that took place the five minutes until then, and from all these counts take the maximum. – Thorsten Kettner Oct 19 '18 at 12:20
Indeed I would just look at the higest number at the 5 minute interval only – ivan Oct 19 '18 at 12:22
I am telling you that "five minute interval" is ambiguous. It may be a fixed interval, like from minute 0 to 5 then to 10 then to 15, ..., or a floating interval. I suppose you want the floating interval, i.e. no matter when some five minutes got the maximum number of calls. (Could be 2018-10-17 14:03:34.08976 to 2018-10-17 14:08:34.08975 for instance.) Then, as I said, take each record and count the calls since five minutes before it. – Thorsten Kettner Oct 19 '18 at 12:38

score 0 · Answer 3 · edited Feb 17 '23 at 15:12

This forces the call time into 5 minute intervals. Use 'count' and 'group by' on these intervals. Using DateTime as a column name is confusing

SELECT DATEADD(MINUTE, CAST(DATEPART(MINUTE, [DateTime] AS INTEGER)%5 * - 1,CAST(FORMAT([DateTime], 'MM/dd/yyyy hh:mm') AS DATETIME)) AS CallInterval, COUNT(*)
FROM Calls
GROUP BY DATEADD(MINUTE, CAST(DATEPART(MINUTE, [DateTime]) AS INTEGER)%5 * - 1,CAST(FORMAT([DateTime], 'MM/dd/yyyy hh:mm') AS DATETIME))

How to get count of average calls every 5 minutes using datetime sql

3 Answers3