Build Record Set for Additional 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, who are using the Additional Text message feature, and places the results in a given record set. You can offer that set to admin and execute a bulk change as needed to turn off additional text notifications.
DECLARE @PatronRecordSetID int;
/* PUT YOUR PATRON RECORD SET ID BELOW */
SET @PatronRecordSetID = 19040
/* 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
EnableSMS = 1
AND
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
/* Prevents collisions */
pr.PatronID NOT IN (
SELECT PatronID FROM Polaris.Polaris.PatronRecordSets
WHERE RecordSetID = @PatronRecordSetID
)
/* Populate the record set. */
INSERT INTO Polaris.Polaris.PatronRecordSets
SELECT * FROM @TempPatronTable