ILL Transaction Report

SELECT
    th.TranClientDate AS [Transaction Date],
    pu.Name AS [Polaris User],
    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)
INNER JOIN -- Get the Polaris user who handled the ILL transaction
    Polaris.Polaris.PolarisUsers pu WITH (NOLOCK) ON pu.PolarisUserID = th.PolarisUserID

WHERE
    th.TransactionTypeID IN (6033,6035,6036)
ORDER BY
    th.TranClientDate,
    illid.numValue