Patron Reading History With NULL ItemRecordIDs

Clearinghouse Repository Polaris Repository
,
1

Patron Reading History With NULL ItemRecordIDs

This is a messy little query that might be useful anyway. If an item has been deleted from the Polaris database, the PatronReadingHistory table will add the title and author so the data will continue to display, even though the item is gone. If you need to tie an item back to a possible, and logical, bibliographic record then this can help. The catch is, if this script can’t find a bibliographic record to match an item to, it won’t display the item in the result list.

SELECT
    prh.PatronID AS "Patron ID",
    p.Barcode AS "Patron Barcode",
    br.BibliographicRecordID AS "Bib Record ID",
    br.BrowseTitle AS "Bib Browse Title",
    br.BrowseAuthor AS "Bib Browse Author",
    prh.BrowseTitle AS "History Browse Title",
    prh.BrowseAuthor AS "History Browse Author",
    prh.CheckOutDate AS "Check Out Date",
    prh.LoaningOrgID AS "Loaning Org Code",
    o.Name AS "Loaning Library"

FROM
    Polaris.Polaris.PatronReadingHistory prh WITH (NOLOCK)
JOIN
    Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) on (prh.BrowseTitle = br.BrowseTitle) and (br.BrowseAuthor like '%' + prh.BrowseAuthor + '%')
JOIN
    Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = prh.PatronID
JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = prh.LoaningOrgID
    
WHERE
    prh.ItemRecordID IS NULL
AND
    prh.PrimaryMARCTOMID = br.PrimaryMARCTOMID

ORDER BY
    prh.CheckOutDate DESC,
    prh.PatronID