Counting date interval on each minutes (Tracking numbers of calls on each minutes)

Question

Working on SQL server (Azure) I have my table of StartCalls and EndCalls (date + time). I would like to build a digramme who count on each minutes the number of calls that were made (Tracking numbers of calls on each minutes)

My solution should be build on my CountTable

CountTable

MinutStart, 
MinuteEnd, 
Range(1 to 60), 
callsnumber

It should be handle all the minutes included on one hour

00:00:00-00:00:01 1 number, 
00:00:01-00:00:02 2 number, 
...

And

Where ( StartCalls > MinutStart ) 
        and ( EndCalls < MinutEnd ) 
UPDATE Callsnumber = Callsnumber+1

Hope somebody has matched this issues and could help ^^

The issue is about the calls that can "overflow" the next hour... No idea for building my history who's counting on each minutes all the calls that are running — ABEF, Oct 04 '16 at 09:05
That's all my Inpout : > >CDR_TIME CDR_DATE CDR_DURATION >11:57:43 AM 02/08/2016 0:01:58 >2:05:47 PM 02/08/2016 0:00:48 >2:06:57 PM 02/08/2016 0:00:50 >4:25:17 PM 02/08/2016 0:01:48 >6:03:47 AM 04/08/2016 0:05:13 >7:05:50 AM 04/08/2016 0:01:27 >7:35:15 AM 04/08/2016 0:01:38 >10:09:41 AM 04/08/2016 0:02:12 >10:58:08 AM 04/08/2016 0:00:27 And I expected to full in my CountingTable by incrementing each field — ABEF, Oct 04 '16 at 09:45
Expected data : incrementing each 60 fields of CountingTable when there is a running call ( so I have to browse a big history ) — ABEF, Oct 04 '16 at 09:51

score 0 · Accepted Answer · edited May 23 '17 at 12:33

0

Well, I would rather go for a view.

Pre-requisite : create a Number table, which would store number from 1 to xxx

see for example this

Than, the idea would be to calculate the duration of your calls. You could then have a starting minute, and whild the call is not at this end, you calculate the next minute and increment by a number.

Of course, you make a modulo 60 to avoid "hours overflow" problem.

You may of course do something better, but you've got the idea.

with calls as( 
 select
  (datepart(minute, startDate) + number -1) % 60 as min1, 
  (datepart(minute, startDate) + number) % 60 as min2, 
  datediff(minute, startDate, endDate) as duration,
  number
 from <YourTable>
 join Numbers n on n.Number <= datediff(minute, startDate, endDate)
)
select 
  min1, 
  min2, 
  count(*) as nbOfCalls
from calls
group by min1, min2
order by min1, min2

EDIT

If you need the detail by year/month/hour/minute, you could do

Create a calendar table, with all minutes, starting on 2016-06-01 in this case (and ending in 2024 in this case).

SELECT TOP 5000000 N=IDENTITY(INT, 1, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);


select minutes = dateAdd(MINUTE, N, '2015-06-01')
into CalendarTable
from Numbers;

Now you can just do

select  
  ct.minutes, 
  count(t.startDate) from 
CalendarTable ct
left join <yourTable> t on ct.minutes BETWEEN DATEADD(mi, DATEDIFF(mi, 0, t.startDate), 0) 
                  AND DATEADD(mi, DATEDIFF(mi, 0,t.EndDate), 0)
--where ct.minutes between '2016-10-03' and '2016-10-05' if you wanna test on an interval, put what you need here
group by minutes
order by minutes

edited May 23 '17 at 12:33

Community

1
1

answered Oct 04 '16 at 10:09

Raphaël Althaus

59,727
6
96
122

Still being unfixed. How to increment the field of the following hour for the overflow calls ? Sample : A call how beging at 01:40 PM and ends at 02:30 PM Should increment the twenty last field of my "Counting table" and the theerty first field for the hour 02:00-03:00 – ABEF Oct 04 '16 at 12:47
Not sure I get it : you want result by minute, not hour ? So for example if a call starts at 19h59 and ends at 20h02, you want a count of 1 for 59-0, 1 for 0-1, and 1 for 1-2, no ? That's what the query (should) do... – Raphaël Althaus Oct 04 '16 at 12:50
Yeah, that's the way. But the 1 for 0-1 and for 1-2, should increase the first field of the next hour and don't know how to deal with. The point is to build a diagramme that track and count on each minutes of each hour on each day, the number of alls calls that are running. – ABEF Oct 04 '16 at 13:10
So correct me if I'm wrong : if you've got a call from 21h59 to 22h00, and one from 22h59 to 23h00, you don't want 59-0 = 2, for example, but 21h59-22h00 = 1 and 22h59-23h00 = 1 ? – Raphaël Althaus Oct 04 '16 at 13:14
Yes, you are right. The diagrame should start on the first Jun 2015 and show us the number of calls that are made on each minutes (zoomable) Example : Between 02 PM and 04 PM we should be able to show the pike time (summit) with the number of calls. 02:01 PM : 5 call running 02:02 PM : 3 call running ... – ABEF Oct 04 '16 at 13:22
You got it perfectly =) – ABEF Oct 04 '16 at 13:40
So take a look at Edit. – Raphaël Althaus Oct 04 '16 at 14:03
No idea to be honest, if that's a kind of reporting tool, you should be able to add parameter to your query, but I don't know Biwee at all... – Raphaël Althaus Oct 04 '16 at 14:18
Thank you Raphaël ! That was a great help – ABEF Oct 04 '16 at 14:56

Counting date interval on each minutes (Tracking numbers of calls on each minutes)

1 Answers1