December 23, 2015 at 8:17 am
I am trying to write a query solution based on the following requirements: "The FinalCalledPartyNumber(voicemail 3699) is called by the same CallingPartyNumber greater than X times within a set period of time." In other words, "Are you trying to hack my voicemail system?"
The columns, and data within are as follows:
ID - identity column (ex. 101045)
Name - (ex. 555-123-4567 -> 3699)
CallingPartyNumber - (ex. 555-123-4567)
FinalCalledPartyNumber - 3699
DateTimeOrigination - date & time of call
Using a self join, the code below will return the result set and fire an alert in the phone system. I need to fire the alert only after 3699 has been call greater than X times in that 5 minute period by the Same CallingPartyNumber.
A kind gentlemen introduced me to use "Count(*) Over" the other day.
The result set that returns is seems to count all the calls to 3699 and when it gets over 10 in 5 minutes it sends the alerts.
I need to limit that set only when same number calls greater than 10 in minutes. Can't figure out if the partition is the way to go.
The syntax below in the first line Can Not be changed, it is system generated SWQL.
SELECT VoipCallDetailsAlert.ID, VoipCallDetailsAlert.Name From VoipCallDetailsAlert
JOIN (
Select *,
Count(*) OVER (PARTITION BY CallingPartyNumber) CPN_count
FROM VoipCallDetailsAlert
WHERE FinalCalledPartyNumber = '3699'
AND DateTimeOrigination > DATEADD(mi, -5, GetDate())
) vcda ON VoipCallDetailsAlert.ID = vcda.ID AND CPN_Count >= 10
Any thoughts would be great. It seems like I am close, but each time I changed the count(*) and fields, I get a different result.
>> Thanks. Dobbs77
BPH
December 23, 2015 at 8:31 am
Removed
gsc_dba
December 23, 2015 at 8:34 am
My bad - will not work on an identity column!
Like this...
SELECT
VoipCallDetailsAlert.ID
, VoipCallDetailsAlert.Name
FROM
VoipCallDetailsAlert
INNER JOIN (
SELECT
VoipCallDetailsAlert.ID
, COUNT(CallingPartyNumber) AS [CPN_count]
FROM
VoipCallDetailsAlert
WHERE
FinalCalledPartyNumber = '3699'
AND DateTimeOrigination > DATEADD(mi, -5, GETDATE())
GROUP BY
VoipCallDetailsAlert.ID
HAVING
COUNT(CallingPartyNumber) >= 10
) vcda
ON VoipCallDetailsAlert.ID = vcda.ID
gsc_dba
December 23, 2015 at 8:38 am
It would be good to see some test data and what you're thinking is the issue.
A few things for testing. First, I'd mock up a test to look for specific data. That isn't everything, but it helps you debug. second, move the "Getdate()" to an assignment at the front, then you can overload this for testing.
select @dt = getdate()
-- select @dt = '20150101 8:45am' -- for tests
I don't think you need the subquery. Those tend to complicate things, but is your subquery returning the correct values?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2015 at 9:07 am
@tencenturies. I initially had a query similar to the one you have posted. ID is an identity column, so Group By ID doesn't work quite right.
This Code below works spot on it stands alone. then I found out that phone app automatically adds the first select statement which needs to be included so the join become necessary.
select
CallingPartyNumber, count(*) as CPN_count
FROM VoipCallDetailsAlert
WHERE (( VoipCallDetailsAlert.[FinalCalledPartyNumber] = '3699')
AND VoipCallDetailsAlert.[DateTimeOrigination] > DATEADD(mi, -5, GetDate()))
group by [CallingPartyNumber]
Having count(*) > 10
the data below illustrates that 6125558706 called 3699 3 Times. the alert would fire at >= 3. Helpful?
ID DatetimeOrigination CallingPartyNumber FinalCalledPartyNumber
105310 2015-12-22 08:21:53.000 6125558706 -> 3699 3699
105311 2015-12-22 08:21:54.000 4125559424 -> 3699 3699
105312 2015-12-22 08:18:46.000 3145556848 -> 6465557875 6465557875
105313 2015-12-22 08:20:56.000 6125558706 -> 3699 3699
105314 2015-12-22 08:22:13.000 3125556848 -> 2145552645 2145552645
105327 2015-12-22 08:21:55.000 6125558706 -> 3699 3699
BPH
December 23, 2015 at 10:17 am
Dobbs77 (12/23/2015)
@tencenturies. I initially had a query similar to the one you have posted. ID is an identity column, so Group By ID doesn't work quite right.This Code below works spot on it stands alone. then I found out that phone app automatically adds the first select statement which needs to be included so the join become necessary.
select
CallingPartyNumber, count(*) as CPN_count
FROM VoipCallDetailsAlert
WHERE (( VoipCallDetailsAlert.[FinalCalledPartyNumber] = '3699')
AND VoipCallDetailsAlert.[DateTimeOrigination] > DATEADD(mi, -5, GetDate()))
group by [CallingPartyNumber]
Having count(*) > 10
the data below illustrates that 6125558706 called 3699 3 Times. the alert would fire at >= 3. Helpful?
ID DatetimeOrigination CallingPartyNumber FinalCalledPartyNumber
105310 2015-12-22 08:21:53.000 6125558706 -> 3699 3699
105311 2015-12-22 08:21:54.000 4125559424 -> 3699 3699
105312 2015-12-22 08:18:46.000 3145556848 -> 6465557875 6465557875
105313 2015-12-22 08:20:56.000 6125558706 -> 3699 3699
105314 2015-12-22 08:22:13.000 3125556848 -> 2145552645 2145552645
105327 2015-12-22 08:21:55.000 6125558706 -> 3699 3699
Using your sample data - this query works:
SELECT
VOIPCallDetailsAlert.id
, *
FROM
VOIPCallDetailsAlert
JOIN (
SELECT
*
, COUNT(*) OVER ( PARTITION BY CallingPartyNumber ) CPN_count
FROM
VOIPCallDetailsAlert
WHERE
FinalCalledPartyNumber = '3699'
--AND DateTimeOrigination > DATEADD(mi, -5, GetDate())
) vcda
ON VOIPCallDetailsAlert.id = vcda.id
AND CPN_count >= 3
I commented out the datetime predicate as I only used the rows you specified but it does work:
id id dateTimeOrigination CallingPartyNumber FinalCalledPartyNumber id dateTimeOrigination CallingPartyNumber FinalCalledPartyNumber CPN_count
1 1 2015-12-23 17:03:19 6125558706 3699 1 2015-12-23 17:03:19 6125558706 3699 3
2 2 2015-12-23 17:03:29 6125558706 3699 2 2015-12-23 17:03:29 6125558706 3699 3
3 3 2015-12-23 17:03:33 6125558706 3699 3 2015-12-23 17:03:33 6125558706 3699 3
I assume you have this running every five minutes to check for existence?
gsc_dba
December 23, 2015 at 10:32 am
@tencenturies. It does work. I'm not certain why it wasn't returning properly when I was running it in phone app earlier this morning. I thought for sure I had syntax wrong. I'll implement this code in the app, and monitor to see that it behaves properly. The DATEADD piece is just for setting time period. If the same number calls 100 time in 5 minutes, you might have a voicemail hack under way.
Thanks for being a second set of eyes for me and helping me out on this. 🙂 Have a great day. Dobbs77
BPH
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply