ILL Activity for a Given Timespan
A library requested a report providing the overall ILL activity for a given time, specifically what was happening, when, where, with whom, and the items involved. This query pulls ILL activity from the TransactionsDatabase based upon usage of the Polaris ILL system. If you’re not using that, then this query won’t help you too much!
-- Variables!
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
-- Set your timeframe below
SET @StartDate = '2022-08-01 00:00:00.000';
SET @EndDate = '2022-08-31 23:59:59.999';
SELECT
th.TranClientDate AS [Transaction Date],
illid.numValue AS [ILL Request ID],
tt.TransactionTypeDescription AS [Action],
title.TransactionString AS [Title],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
porg.DisplayName AS [Patron Branch],
borg.DisplayName AS [ILL Pickup Branch]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Pull the readable Transaction Type
PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK)
ON (tt.TransactionTypeID = th.TransactionTypeID)
INNER JOIN -- Pull the ILL request ID
PolarisTransactions.Polaris.TransactionDetails illid WITH (NOLOCK)
ON (th.TransactionID = illid.TransactionID AND illid.TransactionSubTypeID = 177)
INNER JOIN -- Pull the PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Pull the Patron's OrganizationID
PolarisTransactions.Polaris.TransactionDetails patronorg WITH (NOLOCK)
ON (th.TransactionID = patronorg.TransactionID AND patronorg.TransactionSubTypeID = 13)
INNER JOIN -- Pull the ILL destination branch
PolarisTransactions.Polaris.TransactionDetails illdest WITH (NOLOCK)
ON (th.TransactionID = illdest.TransactionID AND illdest.TransactionSubTypeID = 149)
INNER JOIN -- Set up linkage from TransactionDetails to TransactionStrings to get ILL title
PolarisTransactions.Polaris.TransactionDetails titlelink WITH (NOLOCK)
ON (th.TransactionID = titlelink.TransactionID AND titlelink.TransactionSubTypeID = 49)
INNER JOIN -- Pull the ILL's title
PolarisTransactions.Polaris.TransactionDetailStrings title WITH (NOLOCK)
ON (titlelink.numValue = title.TransactionStringID)
INNER JOIN -- Use the PatronID in Patron Registration
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = patron.numValue)
INNER JOIN -- Use the PatronID in Patrons
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = patron.numValue)
INNER JOIN -- Use the Patron's OrganizationID in Organizations
Polaris.Polaris.Organizations porg WITH (NOLOCK)
ON (porg.OrganizationID = patronorg.numValue)
INNER JOIN -- Use the ILL destination OrganizationID in Organizations
Polaris.Polaris.Organizations borg WITH (NOLOCK)
ON (borg.OrganizationID = illdest.numValue)
WHERE
th.TransactionTypeID IN (6033,6035,6036)
AND
th.TranClientDate BETWEEN @StartDate AND @EndDate
ORDER BY
th.TranClientDate