List of Patron Emails Based on Expiration and Check Out Dates
This query pulls a list of patron email addresses for an email service service like Constant Contact or MailChimp. To filter out long inactive patrons (and possibly bad email addresses), this query looks at expiration dates as well as the last time a patron checked out an item from the library. Dates can be manipulated as needed to get the list you’re after.
SELECT
DISTINCT td.numValue AS [Patron ID],
pr.EmailAddress AS [Email Address],
pr.AltEmailAddress AS [Alt Email Address]
FROM
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK)
JOIN
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK) ON th.TransactionID = td.TransactionID
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = td.numValue
WHERE
th.TransactionTypeID = 6001 -- Check Out
AND
td.TransactionSubTypeID = 6 -- Patron ID
AND
th.TranClientDate BETWEEN '2016-05-01' AND '2021-05-11 23:59:59.999' -- Set your dates here for the last time an item was checked out
AND
th.OrganizationID IN (106,107,108) -- Set Organization IDs as desired
AND
td.numValue IN (
-- This select statement pulls a list of patrons based on expiration dates and uses
-- that as a basis for your list of patrons called in the original select statment.
SELECT
pr.PatronID
FROM
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) on pr.PatronID = p.PatronID
WHERE
p.OrganizationID IN (106,107,108) -- These Organization IDs should match throughout the query
AND
pr.ExpirationDate BETWEEN '2018-05-11' AND '2100-01-01' -- Set your expiration date parameters here
AND
pr.EmailAddress IS NOT NULL -- Only pull patrons with at least one email address.
)