Pull Patrons Last Checkout Date
This is a statistical query that pulls a list of patrons along with their last checkout date. This allows for checking on circ based patron activity rather than relying on the Last Activity Date. Because the Last Activity Date can get updated for any number of reasons that have nothing to do with a circulation based transaction. You can limit the date to a given timeframe if needed or you can change the TransactionTypeID to something else if you wish to track that.
SELECT
DISTINCT(p.PatronID),
o.[Name],
MAX(th.TranClientDate) AS LastCheckOutDate
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON td.TransactionID = th.TransactionID
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = td.numValue
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = p.OrganizationID
WHERE
th.TransactionTypeID = 6001 -- Check out transaction
AND
td.TransactionSubTypeID = 6 -- PatronID in the TransactionDetails table
AND
p.OrganizationID in (5) -- Limit by specific org codes if needed.
GROUP BY
p.PatronID,
o.[Name]
HAVING
MAX(th.TranClientDate) < '2018-04-01 00:00:00.000' -- Set date parameters as desired
ORDER BY
LastCheckOutDate DESC