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
Go to Top of Page

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;
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 !!
Go to Top of Page

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.
Go to Top of Page

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'"
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -