Build Record Set for Text Message Changes
If you’re having issues with a particular mobile carrier, you may want to temporarily switch patrons using that carrier over to email rather than relying on text messages. This query pulls a list of patrons using specified carriers and places the results in a given record set. You can offer that set to admin and execute a bulk change as needed.
DECLARE @PatronRecordSetID int;
/* PUT YOUR PATRON RECORD SET ID BELOW */
SET @PatronRecordSetID = 19038
/* Let's make a temporary table to hold some patron records as a go between for our data and the record set */
DECLARE @TempPatronTable TABLE
(RecordSetID INT,
PatronID INT)
/* Now let's populate that table */
INSERT @TempPatronTable
SELECT
@PatronRecordSetID AS "RecordSetID",
pr.PatronID
FROM
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) on p.PatronID = pr.PatronID
WHERE
pr.DeliveryOptionID IN (8) -- Text notifications
AND
/* Change to Phone2CarrierID and/or Phone3CarrierID to get the others if needed */
pr.Phone1CarrierID IN (1,7,15,23) -- Place your phone carrier IDs here (found in Polaris.Polaris.SA_MobilePhoneCarriers)
AND
p.OrganizationID IN (2,3) -- Limit by OrganizationID codes
AND
pr.EmailAddress IS NOT NULL -- Change this to IS NULL to get patrons without email addresses in place
AND
/* Prevents collisions */
pr.PatronID NOT IN (
SELECT PatronID FROM PatronRecordSets WITH (NOLOCK)
WHERE RecordSetID = @PatronRecordSetID
)
/* Populate the record set. */
INSERT INTO Polaris.Polaris.PatronRecordSets
SELECT * FROM @TempPatronTable