This query pulls the item level history for interlibrary loans (ILL) in Polaris. It’s important to remember that there are probably a dozen ways to handle ILLs that do or do not involve the Polaris ILL module or that partially involve the ILL module. As such, you may need to make some changes to this query to match your workflow.
-- Declare a variable for our PatronID
DECLARE @PatronIDNumber INT = 314159 -- Put your PatronID here
SELECT
patron.numValue AS [PatronID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
th.TranClientDate AS [Transaction Date],
tt.TransactionTypeDescription AS [Transaction],
CASE reqtype.numValue
WHEN 1 THEN 'Loan' -- The ILL is an item borrowed
WHEN 2 THEN 'Copy' -- The ILL is a copy request
ELSE 'Unknown' -- Probably don't need this, but it's a good practice
END AS [ILL Request Type],
illreq.numValue AS [ILLRequestID],
biblio.numValue AS [BibliographicRecordID],
title.TransactionString AS [ILL Title],
pu.Name AS [Polaris User],
o.Name AS [Library/Branch],
w.DisplayName AS [Workstation]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Use the PatronID to get the patron's barcode
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = patron.numValue)
INNER JOIN -- Use the PatronID to get the patron's name
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = patron.numValue)
INNER JOIN -- Human readable transaction description
PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK)
ON (tt.TransactionTypeID = th.TransactionTypeID)
INNER JOIN -- Determine the request type (loan or copy)
PolarisTransactions.Polaris.TransactionDetails reqtype WITH (NOLOCK)
ON (th.TransactionID = reqtype.TransactionID AND reqtype.TransactionSubTypeID = 179)
INNER JOIN -- Get the ILLRequestID
PolarisTransactions.Polaris.TransactionDetails illreq WITH (NOLOCK)
ON (th.TransactionID = illreq.TransactionID AND illreq.TransactionSubTypeID = 177)
LEFT JOIN -- Get the BibliographicRecordID
PolarisTransactions.Polaris.TransactionDetails biblio WITH (NOLOCK)
ON (th.TransactionID = biblio.TransactionID AND biblio.TransactionSubTypeID = 36)
INNER JOIN -- Get the TransactionStringID that will pull the title assigned to the ILL request
PolarisTransactions.Polaris.TransactionDetails tdstitle WITH (NOLOCK)
ON (th.TransactionID = tdstitle.TransactionID AND tdstitle.TransactionSubTypeID = 49)
INNER JOIN -- Get the title placed on the ILL request itself
PolarisTransactions.Polaris.TransactionDetailStrings title WITH (NOLOCK)
ON (title.TransactionStringID = tdstitle.numValue)
INNER JOIN -- Pulls the Polaris username
Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)
ON (pu.PolarisUserID = th.PolarisUserID)
INNER JOIN -- Pulls the transacting branch
Polaris.Polaris.Organizations o WITH (NOLOCK)
ON (o.OrganizationID = th.OrganizationID)
INNER JOIN -- Pulls the workstation info
Polaris.Polaris.Workstations w WITH (NOLOCK)
ON (w.WorkstationID = th.WorkstationID)
WHERE -- ILL item created, active, received, returned, cancelled (respectively)
th.TransactionTypeID BETWEEN 6033 AND 6037
AND -- Set your dates as desired
th.TranClientDate BETWEEN '2025-04-01 00:00:00.000' AND '2025-07-16 23:59:59.999'
AND -- Use that PatronID variable
patron.numValue = @PatronIDNumber
ORDER BY
th.TranClientDate DESC