Last Month's ILL Report

Last Month’s ILL Report

An ILL report requested by one of our libraries that I thought might be useful for other libraries. Pulls a bunch of relevant information about ILLs, the items, the patrons, and so on.

    th.TranClientDate AS [Transaction Date],
    illid.numValue AS [ILL Request ID],
    tt.TransactionTypeDescription AS [Action],
    title.TransactionString AS [Title],
    tom.[Description] AS [Format],
    illreq.StaffNotes1 AS [Staff Notes],
    p.Barcode AS [Patron Barcode],
    pr.PatronFullName AS [Patron Name]

    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 -- Use the ILLRequestID to get Staff Notes 1
    Polaris.Polaris.ILLRequests illreq WITH (NOLOCK)
    ON (illreq.ILLRequestID = illid.numValue)
LEFT JOIN -- Use the ILLRequestID to get the ItemRecordID
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (illreq.ItemRecordID = cir.ItemRecordID)
    Polaris.Polaris.MARCTypeOfMaterial tom WITH (NOLOCK)
    ON (tom.MARCTypeOfMaterialID = illreq.PrimaryMARCTOMID)

WHERE -- ILL related transactions
    th.TransactionTypeID IN (6033,6035,6036)
AND -- Currently set to the start of last month and the end of last month. Adjust to suit.
    th.TranClientDate BETWEEN dateadd(mm,datediff(m,0,getdate())-1,0) AND dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0))