Concurrent Calls from CDR - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Concurrent Calls from CDR

Author

Topic

johnny1
Starting Member

7 Posts

Posted - 2015-04-24 : 07:37:04

Hi, can somebody help me create a query to give me the number of concurrent calls based on a specified set of parameters from a CDR (call detail record) table in a database. Let me explain...

Whenever people dial into a specified dialstring then licenses are used and we need to make sure that we have enough licenses to handle the call volume. That dialstring will start with, for example, 555 and the remaining digits are not important.

My table, called 'cdr' has the following columns:
StartTime (HH:MM:SS 24 hours)
EndTime (HH:MM:SS 24 hours)
StartDate (YYYY-MM-DD)
EndDate (YYYY-MM-DD_
ConnectionTime (in minutes)
DialString (string, ex, 555123)

I tried the following query but it doesn't seem to return a value worth using:

------------------------------------------

SELECT COUNT(*)
FROM
cdr AS c1
JOIN cdr AS c2 ON
c1.StartTime <= c2.EndTime
AND c1.EndTime>= c2.StartTime
WHERE c1.StartDate='2015-04-23' AND c1.DialString like '555%'
GROUP BY
c1.callstart,
c1.callend
ORDER BY COUNT(*) DESC

-----------------------------------------

What i get is output in a single column filled with a string of digits that really means nothing to me... So to re-iterate, using the above example (or new syntax), I need to be able to see concurrent calls with a dialstring starting with '555' in a 24 hour period (date) and I chose yesterday because it's in the database. I will use PHP on a webpage to increment that date so specifying that isn't an issue. And even if those calls only overlap for a few minutes then it's worth noting because it will consume the licensing.

Thanks for your help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 09:21:19

Try adding c1.callstart, c1.callend to your SELECT clause

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-24 : 09:39:16

Number of concurrent calls will change each time a call is started or ended; so are you looking for a result set with one row for each call along with the number of concurrent calls at that time, or are you looking for just one number that gives you the maximum number of concurrent calls where during the entire day? If it is the former, use the following query, and if it is the latter, Add a TOP (1) and order by b.ConcurrentCallsIncludingThis DESC

SELECT
	LEFT(a.DialString,3) AS DialString3,
	a.StartDate,
	a.StartTime,
	b.ConcurrentCallsIncludingThis
FROM
	SomeTable a
	CROSS APPLY
	(
		SELECT COUNT(*) AS ConcurrentCallsIncludingThis
		FROM SomeTable b
		WHERE 
			b.StartDate <= a.StartDate
			AND b.StartTime <= a.StartTime
			AND 
			(
				ISNULL(b.EndDate,'20991231') > a.StartDate
				OR
				(
					ISNULL(b.EndDate,'20991231') = a.StartDate
					AND ISNULL(b.EndTime,'23:59:59.9999999') >= a.EndTime
				)
			)
	) b
WHERE
	a.StartDate = '20150423' 
ORDER BY
	LEFT(a.DialString,3),
	a.StartDate,
	a.StartTime;

johnny1
Starting Member

7 Posts

Posted - 2015-04-24 : 09:47:34

Thanks James, it was the latter - looking for just one number that gives you the maximum number of concurrent calls during the entire day. I will give your script a shot and let you know, thanks again.

johnny1
Starting Member

7 Posts

Posted - 2015-04-28 : 09:59:50

Unfortunately, that didn't work. What I see are 4 columns, the dialstring, the start date, the start time and a 4th column "ConcurrentCallsINcludingThis" which have values that can't be the concurrent call value.

What I really need to see is max concurrent calls for a specific dialstring on a daily basis. And that value will likely be fairly low.

johnny1
Starting Member

7 Posts

Posted - 2015-04-28 : 10:08:33

quote:
Originally posted by johnny1

Unfortunately, that didn't work. What I see are 4 columns, the dialstring, the start date, the start time and a 4th column "ConcurrentCallsINcludingThis" which have values that can't be the concurrent call value.

What I really need to see is max concurrent calls for a specific dialstring on a daily basis. And that value will likely be fairly low.

Nevermind that worked, i forgot to add Top(1) thanks !!

johnny1
Starting Member

7 Posts

Posted - 2015-04-28 : 10:36:59

I think I spoke too soon.. So it looks like I am getting a value which shows concurrent calls for ALL phone numbers and I need to focus on just a particular dialstring. We're close.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-28 : 11:58:50

Add that to the WHERE in BOTH the WHERE clauses. "AND LEFT(a.DialString,3) = '555'"

johnny1
Starting Member

7 Posts

Posted - 2015-04-28 : 14:26:13

I actually get a value higher than all the calls made from that dial string. A simple select * where the dialstring is like 555 for a particular day gives me a value smaller than what the query is returning.

johnny1
Starting Member

7 Posts

Posted - 2015-04-28 : 14:47:10

correction, i get a value EQUAL to all of the calls made for that dialstring. it's as if the query isn't looking at concurrent calls but just all calls.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources