Patron Claims History

Patron Claims History

So far as I know, there’s no good way to pull a claims history through Polaris without using SQL. This query addressed a librarian’s request that we find out what a patron claimed, when, how often claims occurred and more. Given the nature of the query it can take a little time to run, depending on the patron’s activity, so set it up, fire it off, and be patient.

-- Set up a variable

-- Drop in your PatronID below
SET @PatronID = 123456;

    th.TranclientDate AS [Transaction Date],
    tt.TransactionTypeDescription AS [Claim Action],
    o.Name AS [Claiming Branch],
    pu.Name AS [Polaris User],
    p.Barcode AS [Patron Barcode],
    pr.PatronFullName AS [Patron Name],
    prb.Name AS [Patron Branch],
    cir.ItemRecordID AS [Item Record ID],
    cir.Barcode AS [Item Barcode],
    iab.Name AS [Item Assigned Branch],
    br.BibliographicRecordID AS [Bib Record ID],
    br.BrowseTitle AS [Title],
    br.BrowseAuthor AS [Author]

    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)

INNER JOIN -- Get a readable transaction type
    PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK)
    ON (th.TransactionTypeID = tt.TransactionTypeID)
INNER JOIN -- Pulls the PatronID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
    ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Joins the PatronID to PatronRegistration for further info
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
    ON (pr.PatronID = patron.numValue)
INNER JOIN -- Brings in the Patron table for barcode and registered branch
    Polaris.Polaris.Patrons p WITH (NOLOCK)
    ON (p.PatronID = pr.PatronID)
INNER JOIN -- Joins the Patron table to Organizations to get patron registered branch
    Polaris.Polaris.Organizations prb WITH (NOLOCK)
    ON (prb.OrganizationID = p.OrganizationID)
INNER JOIN -- Pulls the ItemRecordID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
    ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN -- Joins the ItemRecordID to CircItemRecords for further info
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (cir.ItemRecordID = item.numValue)
INNER JOIN -- Pulls the BibliographicRecordID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails bib WITH (NOLOCK)
    ON (th.TransactionID = bib.TransactionID AND bib.TransactionSubTypeID = 36)
INNER JOIN -- Joins the BibliographicRecords table to get title and author info
    Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
    ON (br.BibliographicRecordID = bib.numValue)
INNER JOIN -- Pulls the item's assigned branch from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails itembranch WITH (NOLOCK)
    ON (th.TransactionID = itembranch.TransactionID AND itembranch.TransactionSubTypeID = 125)
INNER JOIN -- Applies the Organizations tablet to get a name for the item assigned branch
    Polaris.Polaris.Organizations iab WITH (NOLOCK)
    ON (iab.OrganizationID = itembranch.numValue)
INNER JOIN -- Joins the Organizations table to get the branch where the transaction occurred
    Polaris.Polaris.Organizations o WITH (NOLOCK)
    ON (th.OrganizationID = o.OrganizationID)
INNER JOIN -- Pulls in the PolarisUsers to get a username
    Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)
    ON (th.PolarisUserID = pu.PolarisUserID)

WHERE -- The following IDs are for claims activity
    th.TransactionTypeID IN (6026,6027,6028,6029,6050)
AND -- Drops the variable into the query
    patron.numValue = @PatronID

    th.TranClientDate ASC