SQL Server related issue

Question

I have this thing that I need to do, and some advice would be greatly appreciated. I have a SQL server table with some phone calls. For every phone call, I have a start and end time.

What I need to do: a stored procedure, which for a certain period of time, say, 5 hours with an interval of x, lets say that 2 minutes returns the number of connected calls.

Something like: Interval Nr of Calls Connected 01-01-2010 12:00:00 - 01-01-2010 12:05:00 30 01-01-2010 12:05:01 - 01-01-2010 12:10:00 10 ............. 

What would be the fastest way to do this? Thank you for your help.

Answer 1

This will work for intervals that have calls ...

 Declare @datetimestart datetime Declare @interval int Set @datetimestart = '2009-01-01 12:00:00' Set @interval = 5 --in minutes Select [start_interval], [end_interval] , count([start_interval]) as [calls] From ( Select DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval ,@datetimestart) , DateAdd( Minute,@interval + Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval ,@datetimestart) From yourTable ) As W([start_interval],[end_interval]) group by [start_interval], [end_interval] 

This will work for all intervals, regardless of the number of calls .

 Declare @datetimestart datetime, @datetimeend datetime, @datetimecurrent datetime Declare @interval int Set @datetimestart = '2009-01-01 12:00:00' Set @interval = 10 Set @datetimeend = (Select max([date]) from yourtable) SET @datetimecurrent = @datetimestart declare @temp as table ([start_interval] datetime, [end_interval] datetime) while @datetimecurrent < @datetimeend BEGIN insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent) set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent) END Select * From ( Select [start_interval],[end_interval], count(d.[start_time]) From @temp t left join yourtable d on d.[start_time] between t.[start_interval] and t.[end_interval] ) As W([start_interval],[end_interval], [calls]) 

Answer 2

I changed the Gaby example a bit. What did you expect

 Declare @datetimeend datetime ,@datetimecurrent datetime ,@interval int Set @interval = 10 Set @datetimeend = (Select max([end_time]) from Calls) SET @datetimecurrent = '2010-04-17 14:20:00' declare @temp as table ([start_interval] datetime, [end_interval] datetime) while @datetimecurrent < @datetimeend BEGIN insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent) set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent) END Select [start_interval],[end_interval], count(d.id) [COUNT] From @temp t left join Calls d on d.end_time >= t.start_interval AND d.start_time <= t.end_interval GROUP BY [start_interval],[end_interval] 

used this to create a table and populate it

 CREATE TABLE dbo.Calls ( id int NOT NULL IDENTITY (1, 1), start_time datetime NOT NULL, end_time datetime NULL, caller nvarchar(50) NULL, receiver nvarchar(50) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Calls ADD CONSTRAINT PK_Calls PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO DECLARE @I INT SET @I = 0 WHILE @I < 100 BEGIN INSERT INTO Calls (start_time, end_time) select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-10,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-9,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-9,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-8,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-8,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-7,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-7,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-6,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-6,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-5,GETDATE())) UNION SELECT DATEADD(HOUR, -@I ,DATEADD(MINUTE,-5,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-4,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-4,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-3,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-3,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-2,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-2,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-1,GETDATE())) UNION select DATEADD(HOUR, -@I ,DATEADD(MINUTE,-1,GETDATE())) ,DATEADD(HOUR, -@I ,DATEADD(MINUTE,-0,GETDATE())); SET @I = @I + 1 END 

Executed in SQL Server 2008 but the script will work in other versions

Answer 3

I would use the Numbers pivot table to get time intervals and then count all calls that span the interval:

 SELECT Intervals.IntervalStart ,Intervals.IntervalEnd ,COUNT(*) FROM ( SELECT DATEADD(MINUTE, Numbers * 2, @StartTime) AS IntervalStart ,DATEADD(MINUTE, (Numbers + 1) * 2, @StartTime) AS IntervalEnd FROM Numbers WHERE Numbers BETWEEN 0 AND (5 * 60 / 2) ) AS Intervals LEFT JOIN Calls ON Calls.CallEnd >= Intervals.IntervalStart AND Calls.CallStart < Intervals.IntervalEnd GROUP BY Intervals.IntervalStart ,Intervals.IntervalEnd 

To get empty intervals, you will need a LEFT JOIN for this from the other Intervals view.

Answer 4

How about this approach:

 select Year(StartTime) as Year, Month(StartTime) as Month, Day(StartTime) as Day, datepart(hh, StartTime) as Hour, datepart(mm, StartTime) / 2 as TwoMinuteSegment, count(*) from MyTable where StartDate between '01-01-2010 12:00:00' and '01-01-2010 17:00:00' group by Year(StartTime), Month(StartTime), Day(StartTime), datepart(hh, StartTime), datepart(mm, StartTime) / 2