Circ Counts for All Items in a Record Set

Clearinghouse Repository Polaris Repository
, ,
1

This is a reworking of the Circ Stats by Items in a Record Set, crafted by Trevor Diamond (MAIN). Rather than pulling total circ stats for the whole of the record set,this query returns circ counts for every item in the record set, including the renewals.

If you don’t want to count renewals separately, comment out the CASE statement in the SELECT, the LEFT JOIN to PolarisTransactions based on TransactionSubTypeID 124, and the CASE statement in the GROUP BY. Renewals will still be counted, but they won’t be broken out into a separate category.

Note: This query will only work with item record sets.

-- Drop the RecordSetID for your item record set below
DECLARE @ItemRecordSetID INT = 1598

SELECT
	irs.ItemRecordID AS [ItemRecordID],
	cir.Barcode AS [Item Barcode],
	br.BrowseTitle AS [Title],
	br.BrowseAuthor AS [Author],
	mat.Description AS [Material Type],
	c.Name AS [Collection],
	CASE renew.numValue WHEN '1' THEN 'Renewal' ELSE 'Check Out' END AS [Circ Type],
	COUNT(DISTINCT th.TransactionID) AS [Circ Count]
FROM
	PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the ItemRecordID out of the TransactionDetails
	PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
	ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN -- See if the checkout was a renewal
    PolarisTransactions.Polaris.TransactionDetails renew WITH (NOLOCK)
    ON (th.TransactionID = renew.TransactionID AND renew.TransactionSubTypeID = 124)
INNER JOIN -- Bring in the ItemRecordSets
	Polaris.Polaris.ItemRecordSets irs WITH (NOLOCK)
	ON (irs.ItemRecordID = item.numValue)
INNER JOIN -- Bring in item data
	Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
	ON (cir.ItemRecordID = irs.ItemRecordID)
INNER JOIN -- Bring in bibliographic data
	Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
	ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN -- Bring in material types
	Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
	ON (mat.MaterialTypeID = cir.MaterialTypeID)
LEFT JOIN -- Bring in collections
	Polaris.Polaris.Collections c WITH (NOLOCK)
	ON (c.CollectionID = cir.AssignedCollectionID)
WHERE -- Checkouts
	th.TransactionTypeID = 6001
AND -- Adjust dates as desired
	th.TranClientDate BETWEEN '2025-01-01 00:00:00.000' AND '2025-07-31 23:59:59.999'
AND -- Use our variable
	irs.RecordSetID = @ItemRecordSetID
GROUP BY
	irs.ItemRecordID,
	cir.Barcode,
	br.BrowseTitle,
	br.BrowseAuthor,
	mat.Description,
	c.Name,
	CASE renew.numValue WHEN '1' THEN 'Renewal' ELSE 'Check Out' END
ORDER BY
	irs.ItemRecordID
4 Likes