Get Item History Based on ItemRecordID

Clearinghouse Repository Polaris Repository
,
1

Get Item History Based on ItemRecordID

This query pulls a basic transaction history (from the Transactions database) for a given item based upon the ItemRecordID. While SimplyReports can produce a similar report, it does so based only upon a barcode. If the item has been deleted, then the barcode won’t work. If you can get an ItemRecordID from the PatronAcctDeletedItemRecordstable, you can plug that into this query to get information about its history.

DECLARE @ItemRecordID INT

SET @ItemRecordID = 466683 -- Put your ItemRecordID here.

SELECT
    th.TransactionID AS [Transaction ID],
    tt.TransactionTypeDescription AS [Transaction],
    o.Name AS [Library],
    pu.Name AS [Polaris User],
    w.DisplayName AS [Workstation],
    td2.numValue AS [Patron ID],
    pr.PatronFullName AS [Patron Name],
    pc.[Description] AS [Patron Code],
    p.Barcode AS [Patron Barcode],
    th.TranClientDate AS [Transaction Date]
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
    PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK) ON tt.TransactionTypeID = th.TransactionTypeID
INNER JOIN
    PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON (th.TransactionID = td.TransactionID AND td.TransactionSubTypeID = 38) -- ItemRecordID
LEFT JOIN
    PolarisTransactions.Polaris.TransactionDetails td2 WITH (NOLOCK) ON (th.TransactionID = td2.TransactionID AND td2.TransactionSubTypeID = 6) -- PatronID
LEFT JOIN
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = td2.numValue
LEFT JOIN
    Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = pr.PatronID
LEFT JOIN
    Polaris.Polaris.PatronCodes pc WITH (NOLOCK) ON pc.PatronCodeID = p.PatronCodeID
INNER JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = th.OrganizationID
INNER JOIN
    Polaris.Polaris.PolarisUsers pu WITH (NOLOCK) on pu.PolarisUserID = th.PolarisUserID
LEFT JOIN
    Polaris.Polaris.Workstations w WITH (NOLOCK) ON w.WorkstationID = th.WorkstationID
WHERE
    td.numValue = @ItemRecordID
ORDER BY
    [Transaction Date] DESC