Optimize Phone Number Lookup...CASE WHEN? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Optimize Phone Number Lookup...CASE WHEN?

Author

Topic

ADJuto
Starting Member

2 Posts

Posted - 2014-10-07 : 16:41:32

Systems:
-Call Center phone system: incoming calls accompanied by the source number, aka Caller ID, aka "ANI"
-SQL Server 2005 - the "data warehouse" which stores customer phone numbers ([cANI]), cust names, location, etc. in a table [CustDataByANI]
-Stored Procedure - call center software passes the Caller ID (aka ANI) to a SP as a parameter which uses it to perform a presently simple SELECT statement on the CustDataByANI table....WHERE [cANI] = @ANI.
This works wonderfully, provided the query finds an exact match between the @ANI and one of the 686K cANI values contained in the 'lookup table'. This happens only about 12% of the time.

Goal: to increase the number of successful "likely/probable" matches

Important note: we are using a global data set and cannot enforce rules regarding the length of either value (the parameter @ANI or the values within [cANI]. In other words RIGHT..10 won't work).

Here are 3 scenarios that I'd like the stored procedure to handle:

Case 1:
The phone system transmits the source number '9876543210' which is used as the parameter @ANI
That exact number exists within the [cANI] column of the CustDataByANI table (record# 55555)
The Select statement returns the values from a number of other columns affiliated with record 55555
Super-easy: WHERE [cANI] = @ANI is successful.

Case 2:
@ANI = '499876543210' (same as above but with a leading '49')
No exact match found within CustDataByANI.cANI
Closest match in [cANI] is '9876543210' (still record 55555)
Even a child would recognize that the only difference from Case 1 is the presence of a 2-digit 'prefix' in the parameter @ANI - probably the country code for Germany (=49).
Such prefixes could be 1 or 2 or even 3 digits in length....we can't predict. We do not want to consider prefixes longer than 3, but in this case do want to return the values from record 55555 as in Case 1.

Case 3: the 'reverse' of Case 2
@ANI = '9876543210'
No exact match found within CustDataByANI.cANI
Closest match in [cANI] is '499876543210' (record # 55555 now has a '49' country prefix)
Again, we would assume that the two are of substantial equivalence. In this case, the [cANI] value contains the longer sequence due to the prefix, which could be 1 or 2 or even 3 digits in length...we cannot predict. We do not want to consider prefixes longer than 3, but in this case do want to return the values from record 55555 as in Case 1.

Due to the possible variability in the length of each value (@ANI and [cANI]) and my near complete lack of SQL programming, I cannot write a SELECT statement for the stored procedure which takes into account all 3 cases. Simple "LIKE" statements with wildcards seem to fail, and my head is spinning on CASE criteria, CONTAINS, and even REVERSE strategies for 'reading' both the @ANI and the cANI values in a right-to left fashion.

My dream is to return the best probable match between the two.
My Stupid Procedure is below; any and all assistance is greatly appreciated!.

BTW, my source table CustDataByANI does include a RevANI column, which is simply the cANI values in reverse. Initially I had thought that the solution might lie in reversing both the @ANI parameter value and finding the greatest match within the [RevANI] column, thereby leaving any wildcards on the right of each. But I'm still stuck and am not sure if that is the best strategy....

CURRENT SP:

USE [GCC]
GO
/****** Object: StoredProcedure [dbo].[SP_GetCustDataByANI] Script Date: 10/07/2014 07:47:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_GetCustDataByANI]
@ANI varchar(80)
AS
BEGIN

SET NOCOUNT ON;

--Remove leading zeros from the varchar @ANI. I chose this method rather than risking
--the undesirable introduction of exponential notation when long characters are converted to
--integers and back...

IF ((LEN(@ANI) > 1) AND (LEFT(@ANI,1)= '0'))
BEGIN
SET @ANI = REPLACE(LTRIM(REPLACE(@ANI,'0',' ')),' ','0')
END

SELECT Id
,cANI
,cServiceClass
,cCompanyClass
,cContactName
,cContactDivision
,cContactDepartment
,cCompanyName
,cOrganizationName
,cContactCity
,cContactStateTerr
,cContactCountry
,cCompanyIsDistributor
,PrefAgentID
,PrefAgentID_SQUAL
,PrefRegionID_SQUAL
,VIP_CC
,VIP_TS
,TS_ACAT
FROM [dbo].[CustDataByANI]
WHERE ([cANI] = @ANI)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-07 : 18:22:50

[code]ALTER PROCEDURE [dbo].[SP_GetCustDataByANI]
@ANI varchar(80)
AS
BEGIN
SET NOCOUNT ON;

--Remove leading zeros from the varchar @ANI. I chose this method rather than risking
--the undesirable introduction of exponential notation when long characters are converted to
--integers and back...

IF ((LEN(@ANI) > 1) AND (LEFT(@ANI,1)= '0'))
BEGIN
SET @ANI = REPLACE(LTRIM(REPLACE(@ANI,'0',' ')),' ','0')
END

if exists (select *
FROM [dbo].[CustDataByANI]
WHERE ([cANI] = @ANI)
)
SELECT Id
,cANI
,cServiceClass
,cCompanyClass
,cContactName
,cContactDivision
,cContactDepartment
,cCompanyName
,cOrganizationName
,cContactCity
,cContactStateTerr
,cContactCountry
,cCompanyIsDistributor
,PrefAgentID
,PrefAgentID_SQUAL
,PrefRegionID_SQUAL
,VIP_CC
,VIP_TS
,TS_ACAT
FROM [dbo].[CustDataByANI]
WHERE ([cANI] = @ANI)
else
if exists (select *
FROM [dbo].[CustDataByANI]
WHERE (right([cANI], len(@ANI)) = @ANI)
)
SELECT Id
,cANI
,cServiceClass
,cCompanyClass
,cContactName
,cContactDivision
,cContactDepartment
,cCompanyName
,cOrganizationName
,cContactCity
,cContactStateTerr
,cContactCountry
,cCompanyIsDistributor
,PrefAgentID
,PrefAgentID_SQUAL
,PrefRegionID_SQUAL
,VIP_CC
,VIP_TS
,TS_ACAT
FROM [dbo].[CustDataByANI]
WHERE (right([cANI], len(@ANI)) = @ANI)
else
if exists (select *
FROM [dbo].[CustDataByANI]
WHERE (right([@ANI], len(cANI)) = cANI)
)
SELECT Id
,cANI
,cServiceClass
,cCompanyClass
,cContactName
,cContactDivision
,cContactDepartment
,cCompanyName
,cOrganizationName
,cContactCity
,cContactStateTerr
,cContactCountry
,cCompanyIsDistributor
,PrefAgentID
,PrefAgentID_SQUAL
,PrefRegionID_SQUAL
,VIP_CC
,VIP_TS
,TS_ACAT
FROM [dbo].[CustDataByANI]
WHERE (right([@ANI], len(cANI)) = cANI)
end[/code]Be aware that options 2 or 3 could return multiple rows...

No amount of belief makes something a fact. -James Randi

ADJuto
Starting Member

2 Posts

Posted - 2014-10-08 : 09:47:19

Flowing Fount of Yak Knowledge: I was hesitant about the multiple IF...ELSE statements and wondered if seemingly independent and consecutive queries would slow down the fetching and most importantly be noticed by callers who experience lengthy pauses in the middle of the call flow.
But I tried it anyway, with a few adaptations/updates, and.... it works great!
You are a rock star.

Follow-up questions:
1. I agree that the last 2 'IF/ELSE' statements may return multiple records. With 'SET NOCOUNT ON', it is my understanding that the system will only pass the first one encountered. Is that correct?
2. Is there a way to capture/display the 'actual' count of records matching the criteria in a new field, such that it says "yeah, here's the first record matching the criteria, but there were X total records which would've worked too..."
3. Lastly, since I'm on a roll now.... could the SP be altered to use a UNION? The SP above works great, but the [dbo].[CustDataByANI] table is populated with external customers only. It's a pain to update and it doesn't include internal employees. Sure, I can probably add them to the table, but do you think I should create a new table just for employees that won't get updated/overwritten/deleted whenever the CustData table is updated? And if so, could this SP be altered to examine both?

Again, your expertise and timeliness are enviable. Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 10:42:04

1. SET NOCOUNT ON has nothing to do with the rows selected. It controls what output messages the queries produce.
2. COUNT(CASE WHEN <your condition> then 1 end) as ActualCount
3. You can use UNION if you like and all the same columns are present in the UNIONed queries in the same order and with the same types (consider UNION ALL)

btw, if your employees table is updated when customer data changes, there's a problem with your DB design.

Brendt
Starting Member

1 Post

Posted - 2014-10-08 : 19:55:00

Here is an alternate version that you can try. It uses UNION, a different technique for removing leading zeroes, a different set of matching code, a UNION statement, a TOP statement to only return one line, and a common table expression to make it easier to return the total count of possible matching rows.

ALTER PROCEDURE [dbo].[SP_GetCustDataByANI]
   @ANI varchar(80)
AS
BEGIN
   SET NOCOUNT ON;

-- DECLARE @ANI varchar(80) -- Testing code
-- SET @ani = '0000050350035030'

   --Remove leading zeros from the varchar @ANI.
   --I consider this a more elegant solution. If you discover other leading characters
   --that might need stripping, just add them to the bracketed pattern after the zero.
   --Example: PATINDEX('%[^0 .]%', @ANI) returns a pointer to the first character
   --    that is not a zero, a space, or a period/decimal point.

   DECLARE @ptr int

   SELECT @ptr = PATINDEX('%[^0]%', @ANI)
   IF @ptr > 1
       SET @ANI = SUBSTRING(@ANI, @ptr, LEN(@ANI))

   ;WITH MyWorkTable AS (
       SELECT Id
           ,cANI
           ,cServiceClass
           ,cCompanyClass
           ,cContactName
           ,cContactDivision
           ,cContactDepartment
           ,cCompanyName
           ,cOrganizationName
           ,cContactCity
           ,cContactStateTerr
           ,cContactCountry
           ,cCompanyIsDistributor
           ,PrefAgentID
           ,PrefAgentID_SQUAL
           ,PrefRegionID_SQUAL
           ,VIP_CC
           ,VIP_TS
           ,TS_ACAT
       FROM [dbo].[CustDataByANI]
       WHERE
           [revANI] LIKE REVERSE(@ANI) + '%' -- Catches case of prefix on stored ANI only
                                                -- and all exact matches
           AND LEN(cANI) - LEN(@ANI) <= 3     -- handle cases where it is a partial match
                                                    -- and the "prefix" is longer than 3 characters

       UNION ALL

       SELECT Id
           ,cANI
           ,cServiceClass
           ,cCompanyClass
           ,cContactName
           ,cContactDivision
           ,cContactDepartment
           ,cCompanyName
           ,cOrganizationName
           ,cContactCity
           ,cContactStateTerr
           ,cContactCountry
           ,cCompanyIsDistributor
           ,PrefAgentID
           ,PrefAgentID_SQUAL
           ,PrefRegionID_SQUAL
           ,VIP_CC
           ,VIP_TS
           ,TS_ACAT
       FROM [dbo].[CustDataByANI]
       WHERE
           REVERSE(@ANI) LIKE [revANI] + '_%' -- Catches case of prefix on @ANI
           AND LEN(@ANI) - LEN(cANI) <= 3
       )
   SELECT TOP 1 Id
       ,cANI
       ,cServiceClass
       ,cCompanyClass
       ,cContactName
       ,cContactDivision
       ,cContactDepartment
       ,cCompanyName
       ,cOrganizationName
       ,cContactCity
       ,cContactStateTerr
       ,cContactCountry
       ,cCompanyIsDistributor
       ,PrefAgentID
       ,PrefAgentID_SQUAL
       ,PrefRegionID_SQUAL
       ,VIP_CC
       ,VIP_TS
       ,TS_ACAT
       ,(SELECT COUNT(*) FROM MyWorkTable) AS RecordCount
    FROM MyWorkTable
    )
END[font=Trebuchet MS]

** The above is my informed opinion. This is not the Word of God.
** Take all advice provided with sufficient salt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources