Complete Patron Circulation History

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

    p.Barcode AS [Patron Barcode],
    pr.PatronFullName AS [Patron Name],
    th.TranClientDate AS [Transaction Date],
    br.BrowseTitle AS [Browse Title],
    tt.TransactionTypeDescription AS [Transaction]    

    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

    th.TransactionTypeID BETWEEN '6001' and '6999'
    td2.numValue = @Patron
AND -- Adjust dates as needed
    th.TranClientDate BETWEEN @StartDate AND @EndDate

    th.TranClientDate DESC