Complete Patron Circulation History
This query pulls a full patron circulation activity history between two given dates based on a Patron ID. Everything from check outs to check ins, fines and fees, and holds will show up in the results as the query pulls all TransactionIDs between 6001 and 6999. This is an upgraded version of the Patron Check Out History query.
DECLARE @Patron INT -- Set up a variable for PatronID
DECLARE @StartDate DATETIME -- Set up a variable for the earliest date
DECLARE @EndDate DATETIME -- Set up a variable for the latest date
SET @Patron = 230626 -- Put your PatronID here
SET @StartDate = '2021-01-01 00:00:00.000' --Set your start date
SET @EndDate = '2021-12-31 23:59:59.999' --Set your end date
SELECT
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
th.TranClientDate AS [Transaction Date],
br.BrowseTitle AS [Browse Title],
tt.TransactionTypeDescription AS [Transaction]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN --Used to get the item record ID
PolarisTransactions.Polaris.TransactionDetails td1 WITH (NOLOCK) ON td1.TransactionID = th.TransactionID
INNER JOIN --Used to get the patron record ID
PolarisTransactions.Polaris.TransactionDetails td2 WITH (NOLOCK) ON td2.TransactionID = th.TransactionID
INNER JOIN --SubTypeID 38 = The unique item record ID
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK) ON cir.ItemRecordID = td1.numValue AND td1.TransactionSubTypeID = 38
INNER JOIN --SubTypeID 6 = The unique patron record ID
Polaris.Polaris.PatronRegistration pr ON pr.PatronID = td2.numValue AND td2.TransactionSubTypeID = 6
INNER JOIN -- Get patron barcode
Polaris.Polaris.Patrons p WITH (NOLOCK) ON pr.PatronID = p.PatronID
INNER JOIN -- Get Transaction Type info
PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK) ON th.TransactionTypeID = tt.TransactionTypeID
INNER JOIN -- Get the browse title from the bib record
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON cir.AssociatedBibRecordID = br.BibliographicRecordID
WHERE
th.TransactionTypeID BETWEEN '6001' and '6999'
AND
td2.numValue = @Patron
AND -- Adjust dates as needed
th.TranClientDate BETWEEN @StartDate AND @EndDate
ORDER BY
th.TranClientDate DESC