Counting Concurrent Transactions

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)

Counting Concurrent Transactions

Author

Topic

xwbhme
Starting Member

2 Posts

Posted - 2007-12-05 : 14:48:19

I'm looking for some help with a query for telephony application.

I'm trying to find the number of concurrent calls from a table where each call has one row and that row has a call start time and a call end time. Each call lasts from less than 1 minute to 120 minutes. The numbers will then feed into an Excel line graph that shows from midight to midnight for one specific day. The objective is to make sure adequate resources for calling are available across the period.

Since the calls can last less than 1 minute (but very few would not span at least one even minute) the resulting set of numbers would have a value for each minute of the day (1440 rows) some of which will be zero but most of which will have something greater than one.

Average call volume for a particular day is about 3000 calls but will grow to as many as 20,000/day and it still needs to be relatively performant.

For calls that last more than one minute, the call would increment all minutes during the call. For example, if the call begins at 8:04:44 and ends at 8:10:05, the minutes for 8:04, 8:05, 8:06, 8:07, 8:08, 8:09, and 8:10 would all show a count of 1, assuming this was the only call during the period.

I'm sure I'm not the first to have to cross this bridge but my searches for transactions, concurrent transactions, etc. have all led down the wrong paths. Any answers, pointers to articles, or techniques would be very helpful.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 15:00:21

[code]-- Prepare sample data
DECLARE @Sample TABLE (CallStart DATETIME, CallEnd DATETIME)

INSERT @Sample
SELECT '8:04:44', '8:10:05' UNION ALL
SELECT '8:09:15', '8:11:43'

-- Show the expected output
SELECT CONVERT(VARCHAR(5), DATEADD(MINUTE, f.Number, '19000101'), 108) AS theMinute,
COUNT(s.CallStart) AS theCount
FROM F_TABLE_NUMBER_RANGE(0, 1439) AS f
LEFT JOIN @Sample AS s ON f.NUMBER BETWEEN DATEDIFF(MINUTE, '19000101', s.CallStart) % 1440 AND DATEDIFF(MINUTE, '19000101', s.CallEnd) % 1440
GROUP BY f.Number
ORDER BY f.Number[/code]F_TABLE_NUMBER_RANGE can be found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

E 12°55'05.25"
N 56°04'39.16"

xwbhme
Starting Member

2 Posts

Posted - 2007-12-05 : 15:14:56

[End of pulling hair out...]

Thank you very, very much. That works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources