ABEF - 1 year ago 106
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 ^^

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)