SQLTeam.com | Weblogs | Forums

Cross Reference Some Phone Records


#1

Afternoon all,

I've been asked to cross reference some phone records with the SQL database. I've imported the spread sheet into a temporary table with the idea of checking the phone number and returning the name (either employee name or supplier name). I've been staring at this for 2 hours now and I'm a little confused as thow to actually achieve it...

A poor attempt I know, but this is what I've got so far...

DECLARE @Callee_Name VarChar(30)

BEGIN
INSERT INTO TempPhoneRec$([Callee Name])
VALUES (@Callee_Name, (

SELECT EmployeeName
FROM Empoyee 
JOIN TempPhoneRec$ ON 
(REPLACE(empPhoneRec$.Callee, ' ', '')) = (REPLACE(Employee.Phone_LL, ' ', ''))
OR
(REPLACE(empPhoneRec$.Callee, ' ', '')) = (REPLACE(Employee.Phone_Mobile, ' ', ''))
))
END

Any pointers or advice as to which would be the best way to achieve this...?

Thanks

Dave


#2

You are joining two tables Empoyee and TempPhoneRec$, yet there is no reference to TempPhoneRec$ in the join condition. You are referring to another table (?) empPhoneRec$ in the join condition. That seems wrong, and in fact should have given you an error when you try to run it.


#3

Hi James,

Thanks for your reply, correct I hadn't joined the third table, I was trying to keep it simple to get the first part of it working.
No, the code above doesn't execute. Like I said it was a poor attempt and I was getting confused as to the best way to attempt it...

Many thanks

Dave

EDIT:

Doh...! Just noticed I was using INSERT instead of UPDATE. OK Getting a bit closer now...

UPDATE TempPhoneRec$
SET TempPhoneRec$.[Callee Name] =  Employee.EmployeeName
FROM Employees 
JOIN TempPhoneRec$ ON (REPLACE(TempPhoneRec$.Callee, ' ', '')) = (REPLACE(Employees.Phone_LL, ' ', ''))
OR
(REPLACE(TempPhoneRec$.Callee, ' ', '')) = (REPLACE(Employees.Phone_Mobile, ' ', ''))

#4

I read that as a typo

REPLACE(empPhoneRec$.Callee

should be

REPLACE(TempPhoneRec$.Callee


#5

I would suggest:

-- Get data into #TEMP tables
SELECT [Source]='Employee', [Name]=EmployeeName, Phone_LL, Phone_Mobile
INTO #TEMP_Names
FROM Employee

INSERT INTO #TEMP_Names
SELECT [Source]='Supplier', SupplierName, Phone_LL, NULL
FROM Suppliers

--TODO  Import from more sources

SELECT SomePKeyRef, Callee
INTO #TEMP_PhoneRec
FROM TempPhoneRec$

-- Clean up all phone numbers
UPDATE U
SET Phone_LL = REPLACE(Phone_LL, ' ', '')
    , Phone_Mobile = REPLACE(Phone_Mobile, ' ', '')
FROM #TEMP_Names AS U

UPDATE U
SET Callee = REPLACE(Callee, ' ', '')
FROM #TEMP_PhoneRe

--TODO ADD More CleanUp statements as you discover that you need them!

-- Create indexes on #TEMP_Names and #TEMP_PhoneRec on the Phone Number column(s)

SELECT [Source], [Name], Phone_LL, Phone_Mobile, Callee, SomePKeyRef
FROM #TEMP_Names AS N
    JOIN #TEMP_PhoneRec AS P
        ON P.Callee = N.Phone_LL
UNION -- probably faster than using an OR in the JOIN
SELECT [Source], [Name], Phone_LL, Phone_Mobile, Callee, SomePKeyRef
FROM #TEMP_Names AS N
    JOIN #TEMP_PhoneRec AS P
        ON P.Callee = N.Phone_Mobile

#6

Ha! I read that as EmpPhoneRec$.Callee as in something to do with Employees :slight_smile: :slight_smile:


#7

Thanks for your replies,

Spot on, all working now as I hoped...!!

Many thanks

Dave