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
DECLARE @PatronID INT;
-- Drop in your PatronID below
SET @PatronID = 123456;
SELECT
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]
FROM
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
ORDER BY
th.TranClientDate ASC