Update Patron Phone Numbers for Long Distance Changes

Clearinghouse Repository Polaris Repository
,
1

Update Patron Phone Numbers for Long Distance Changes

**!! BE VERY CAREFUL WITH THIS ONE !!**
This query, developed with Gabrielle Gosselin (Richland Library: Columbia, South Carolina), is used to adjust patron phone numbers in preparation for changing settings on the telephony server. In this case, a library was in an area that switched to 10 digit calling and the system is such that it's better to put a 1 before \*all\* telephone numbers, including local ones. I was setting the telephony server to dial 9,1, before all calls, but there was a snag. Some patron phone numbers were already in Polaris with a 1 in front, which meant the system would dial 9,1,1... and after that, the phone number wouldn't matter. With Gabrielle's help (by which I mean she did most of the work), we came up with a query to pull all of the phone numbers with a 1 already in the prefix, and then remove that 1 before writing the updated number back to the database. I recommend snapshotting your patron registration data before running this query, and try it out first on a training server. You may need to adjust things to suit your data.

You will also need to run this query three times. Once for PhoneVoice1 and again for PhoneVoice2 and a third time for PhoneVoice3.

Big thanks again to Gabrielle!

/* Run this first part on its own so you make sure you're getting back data that makes sense. */

SELECT
    pr.PatronID,
    pr.PhoneVoice1,
    RIGHT(pr.PhoneVoice1,12) AS [NewPhoneVoice1]
INTO
    Polaris.Polaris.TempPhoneUpdate
FROM
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
WHERE
    pr.PhoneVoice1 LIKE '1%'
AND
    LEN(pr.PhoneVoice1) = 14 -- THIS NUMBER MAY NEED ADJUSTMENT

/*
Next you update the new phone numbers in the temp table to the live patron registration numbers.
I've commented out the UPDATE for safety.
*/

-- UPDATE
    Polaris.Polaris.PatronRegistration
SET
    PatronRegistration.PhoneVoice1 = TempPhoneUpdate.NewPhoneVoice1
FROM
    Polaris.Polaris.TempPhoneUpdate
WHERE
    PatronRegistration.PatronID = TempPhoneUpdate.PatronID

DROP TABLE Polaris.Polaris.TempPhoneUpdate