Complete Item Circulation History

Complete Item Circulation History

This query produces a complete item circulation history based on an ItemRecordID. Basically, it pulls anything from the Transactions database with a TransactionTypeID between 6001 and 6999. It’ll relay the item’s browse title, the transaction type (check out, check in, charges, payments, etc), the patron name associated with the transaction, and the patron’s barcode and PatronID.

DECLARE @ItemID INT --Set up a variable

SET @ItemID = 396787 --Put your ItemRecordID here
SET @StartDate = '2021-01-01 00:00:00.000'
SET @EndDate = '2021-12-31 23:59:59.999'

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

    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 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 WITH (NOLOCK) 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'
    td1.numValue = @ItemID
AND -- Adjust dates as needed
    th.TranClientDate BETWEEN @StartDate AND @EndDate

    th.TranClientDate DESC