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
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
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'
SELECT
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]
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 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
WHERE
th.TransactionTypeID between '6001' and '6999'
AND
td1.numValue = @ItemID
AND -- Adjust dates as needed
th.TranClientDate BETWEEN @StartDate AND @EndDate
ORDER BY
th.TranClientDate DESC