Circ Stats by Items in a Record Set

Clearinghouse Repository Polaris Repository
,
1

Circ Stats by Items in a Record Set

This query will pull circulation stats, checkouts and renewals, for items contained in a given item record set. Useful for tracking circ stats on displays, special collections, etc.
```SQL SELECT CASE td2.numValue WHEN '1' THEN 'Renewal' ELSE 'Check Out' END, COUNT(*) as "Circ Count"

FROM
PolarisTransactions.Polaris.TransactionHeaders AS [th] WITH (NOLOCK)

INNER JOIN
PolarisTransactions.Polaris.TransactionDetails AS [td1] WITH (NOLOCK) ON th.TransactionID = td1.TransactionID AND td1.TransactionSubTypeID = ‘38’
INNER JOIN
Polaris.Polaris.ItemRecordSets AS [irs] WITH (NOLOCK) ON td1.numValue = irs.ItemRecordID
LEFT OUTER JOIN
PolarisTransactions.Polaris.TransactionDetails AS [td2] WITH (NOLOCK) ON th.TransactionID = td2.TransactionID AND td2.TransactionSubTypeID = ‘124’

WHERE
th.TransactionTypeID = ‘6001’

– SET CIRC DATES BELOW
AND th.TranClientDate BETWEEN ‘2020-02-01 00:00:00.000’ AND ‘2020-02-28 23:59:59’

AND
irs.RecordSetID = ‘1598’ – Put your ItemRecordSetID here

GROUP BY
CASE td2.numValue WHEN ‘1’ THEN ‘Renewal’ ELSE ‘Check Out’ END