List of Items Checked In Through In-House Check In

Clearinghouse Repository Polaris Repository
, ,
1

This query will pull a list of items that have been checked in via in-house check in between two given dates. Along with branch, title, and author information, the subjects for each item are also included in a single column within the results.

SELECT
    DISTINCT itemrecord.numValue AS [Item Record],
    ab.Name AS [Assigned Branch],
    br.BrowseTitle AS [Title],
    br.BrowseAuthor AS [Author],
    ird.CallNumber AS [Call Number],
    mat.Description AS [Material Type],
    STUFF((
        SELECT ', ' + msh.BrowseSubject
        FROM Polaris.Polaris.BibliographicRecords br_inner
        INNER JOIN Polaris.Polaris.BibSubjectIndices bsi_inner ON bsi_inner.BibliographicRecordID = br_inner.BibliographicRecordID
        INNER JOIN Polaris.Polaris.MainSubjectHeadings msh ON msh.MainSubjectHeadingID = bsi_inner.MainSubjectHeadingID
        WHERE br_inner.BibliographicRecordID = br.BibliographicRecordID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS [Subject Headings] -- Compatible with SQL Server 2016
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Pulls the checkin type from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails checkintype WITH (NOLOCK)
    ON (th.TransactionID = checkintype.TransactionID AND checkintype.TransactionSubTypeID = 128)
INNER JOIN -- Pulls the ItemRecordID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails itemrecord WITH (NOLOCK)
    ON (th.TransactionID = itemrecord.TransactionID AND itemrecord.TransactionSubTypeID = 38)
LEFT JOIN -- Links the ItemRecordID
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (cir.ItemRecordID = itemrecord.numValue)
LEFT JOIN -- Links the BibliographicRecordID
    Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
    ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
LEFT JOIN -- Bring in item record details to get call number
    Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK)
    ON (ird.ItemRecordID = itemrecord.numValue)
LEFT JOIN -- Bring in the material type
    Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
    ON (mat.MaterialTypeID = cir.MaterialTypeID)
LEFT JOIN -- Pulls in assigned branch
    Polaris.Polaris.Organizations ab WITH (NOLOCK)
    ON (ab.OrganizationID = cir.AssignedBranchID)
LEFT JOIN -- Pulls in Bibliographic Subject Indices
    Polaris.Polaris.BibSubjectIndices bsi WITH (NOLOCK)
    ON (bsi.BibliographicRecordID = br.BibliographicRecordID)
LEFT JOIN -- Pulls in subject headings
    Polaris.Polaris.MainSubjectHeadings msh WITH (NOLOCK)
    ON (msh.MainSubjectHeadingID = bsi.MainSubjectHeadingID)
WHERE -- Check ins
    th.TransactionTypeID = 6002
AND -- Staff Client In-House and Leap In-House Check In
    checkintype.numValue IN (6,56)
AND -- Set your dates
    th.TranClientDate BETWEEN '2024-02-01 00:00:00.000' AND '2024-12-31 23:59:59.999'
ORDER BY
    br.BrowseTitle,
    br.BrowseAuthor