ILL Activity for a Given Timespan

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!

-- Set your timeframe below
SET @StartDate = '2022-08-01 00:00:00.000';
SET @EndDate = '2022-08-31 23:59:59.999';

    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]

    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)

    th.TransactionTypeID IN (6033,6035,6036)
    th.TranClientDate BETWEEN @StartDate AND @EndDate