Pull Record Set Information and Counts

Clearinghouse Repository Polaris Repository
, , ,
1

This query pulls information and counts about the record sets in your Polaris system, including all four kinds of record sets.

WITH ItemCounts AS (
    SELECT RecordSetID, COUNT(*) AS ItemCount
    FROM Polaris.Polaris.ItemRecordSets WITH (NOLOCK)
    GROUP BY RecordSetID
),
BibCounts AS (
    SELECT RecordSetID, COUNT(*) AS BibCount
    FROM Polaris.Polaris.BibRecordSets WITH (NOLOCK)
    GROUP BY RecordSetID
),
PatronCounts AS (
    SELECT RecordSetID, COUNT(*) AS PatronCount
    FROM Polaris.Polaris.PatronRecordSets WITH (NOLOCK)
    GROUP BY RecordSetID
),
AuthCounts AS (
	SELECT RecordSetID, COUNT(*) AS AuthCount
	FROM Polaris.Polaris.AuthorityRecordSets WITH (NOLOCK)
	GROUP BY RecordSetID
)
SELECT
    rs.RecordSetID AS [RecordSetID],
    rs.Name AS [Record Set Name],
    rs.CreationDate AS [Creation Date],
    rs.ModificationDate AS [Last Modified],
    pu.Name AS [Record Set Owner],
    ot.Description AS [Type],
    rs.Note AS [Note],
    rss.RecordStatusName AS [Status],
    COALESCE(ic.ItemCount, 0) AS [Item Count],  -- Item counts
    COALESCE(bc.BibCount, 0) AS [Bib Count],  -- Bib counts
    COALESCE(pc.PatronCount, 0) AS [Patron Count],  -- Patron counts
    COALESCE(ac.AuthCount, 0) AS [Authorization Count]  -- Authorization counts
FROM
    Polaris.Polaris.RecordSets rs WITH (NOLOCK)
INNER JOIN
    Polaris.Polaris.ObjectTypes ot WITH (NOLOCK)
    ON ot.ObjectTypeID = rs.ObjectTypeID
INNER JOIN
    Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)
    ON pu.PolarisUserID = rs.CreatorID
INNER JOIN
    Polaris.Polaris.RecordStatuses rss WITH (NOLOCK)
    ON rss.RecordStatusID = rs.RecordStatusID
LEFT JOIN
    ItemCounts ic 
    ON (ic.RecordSetID = rs.RecordSetID)
LEFT JOIN
    BibCounts bc
    ON (bc.RecordSetID = rs.RecordSetID)
LEFT JOIN
    PatronCounts pc
    ON (pc.RecordSetID = rs.RecordSetID)
LEFT JOIN
	AuthCounts ac
	ON (ac.RecordSetID = rs.RecordSetID)
ORDER BY
    rs.RecordSetID DESC;
1 Like