SQL to Determine Max # of Records at Specific Time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL to Determine Max # of Records at Specific Time

Author

Topic

stillmc
Starting Member

2 Posts

Posted - 2012-09-05 : 10:20:24

I need assistance on a complex query. I have a table of phone calls made throughout the day. The table consists of date, start time and duration of phone calls. I need to write a query which will summarize the data to show me the maximum number of concurrent phone calls at one time (and as a bonus, when this was). The query needs to take into account the start time of the call and the duration to determine how many calls were being conducted at specific points in time to determine the maximum calls. Anyone know how to accomplish this? TIA

Based on this sample data, I believe the maximum is 4 calls on 8/12 at 16:47.

Sample data:

08-10-2012 15:31 00:00:03
08-10-2012 15:32 00:01:00
08-10-2012 15:32 00:00:58
08-10-2012 15:31 00:02:03
08-10-2012 15:35 00:00:06
08-10-2012 15:34 00:02:03
08-10-2012 15:34 00:03:03
08-10-2012 15:38 00:00:12
08-10-2012 15:38 00:00:03
08-10-2012 15:32 00:07:03
08-12-2012 16:28 00:00:30
08-12-2012 16:28 00:01:42
08-12-2012 16:29 00:00:26
08-12-2012 16:26 00:07:11
08-12-2012 16:33 00:00:07
08-12-2012 16:30 00:03:28
08-12-2012 16:41 00:00:21
08-12-2012 16:42 00:00:39
08-12-2012 16:42 00:02:05
08-12-2012 16:45 00:00:54
08-12-2012 16:45 00:02:44
08-12-2012 16:47 00:01:22
08-12-2012 16:47 00:02:06
08-12-2012 16:45 00:04:40
08-12-2012 16:49 00:01:01
08-12-2012 16:51 00:00:21

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-05 : 11:33:15

Code below uses starting time as reference to determine the count - and I am not seeing the result you indicated, so I may have misunderstood it.

CREATE TABLE #tmp (id INT NOT NULL IDENTITY(1,1), StartTime DATETIME, duration TIME, EndTime as StartTime + duration);
INSERT INTO #tmp VALUES 
('08-10-2012 15:31','00:00:03'),
('08-10-2012 15:32','00:01:00'),
('08-10-2012 15:32','00:00:58'),
('08-10-2012 15:31','00:02:03'),
('08-10-2012 15:35','00:00:06'),
('08-10-2012 15:34','00:02:03'),
('08-10-2012 15:34','00:03:03'),
('08-10-2012 15:38','00:00:12'),
('08-10-2012 15:38','00:00:03'),
('08-10-2012 15:32','00:07:03'),
('08-12-2012 16:28','00:00:30'),
('08-12-2012 16:28','00:01:42'),
('08-12-2012 16:29','00:00:26'),
('08-12-2012 16:26','00:07:11'),
('08-12-2012 16:33','00:00:07'),
('08-12-2012 16:30','00:03:28'),
('08-12-2012 16:41','00:00:21'),
('08-12-2012 16:42','00:00:39'),
('08-12-2012 16:42','00:02:05'),
('08-12-2012 16:45','00:00:54'),
('08-12-2012 16:45','00:02:44'),
('08-12-2012 16:47','00:01:22'),
('08-12-2012 16:47','00:02:06'),
('08-12-2012 16:45','00:04:40'),
('08-12-2012 16:49','00:01:01'),
('08-12-2012 16:51','00:00:21')

SELECT DISTINCT 
	a.StartTime,
	b.N
FROM
	#tmp A
	CROSS APPLY
	(
		SELECT COUNT(*) AS N
		FROM #tmp c
		WHERE c.EndTime >= a.StartTime
		AND c.StartTime <= a.StartTime
	) b

stillmc
Starting Member

2 Posts

Posted - 2012-09-06 : 08:17:38

Thank you. This worked perfectly!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 12:01:16

You are very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources