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
Range(1 to 60),
00:00:00-00:00:01 1 number,
00:00:01-00:00:02 2 number,
Where ( StartCalls > MinutStart )
and ( EndCalls < MinutEnd )
UPDATE Callsnumber = Callsnumber+1
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
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