ABEF ABEF - 2 months ago 14
SQL Question

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

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 ^^

Answer

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