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. */
RIGHT(pr.PhoneVoice1,12) AS [NewPhoneVoice1]
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
pr.PhoneVoice1 LIKE '1%'
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.
PatronRegistration.PhoneVoice1 = TempPhoneUpdate.NewPhoneVoice1
PatronRegistration.PatronID = TempPhoneUpdate.PatronID
DROP TABLE Polaris.Polaris.TempPhoneUpdate