This query was requested on the IUG Discord and supplied by @mhammermeister at Pinnacle (IL). It produces a report of checkouts for unique patrons based on collection. In other words, how many distinct patrons account for circulation in Adult Fiction, or in Children’s Non-Fiction?
Big thanks to Matt for this query!
SELECT
C.Name,
COUNT(DISTINCT TDpat.numValue) AS [Patrons],
COUNT(DISTINCT TH.TransactionID) AS [Circs]
FROM
PolarisTransactions.Polaris.TransactionHeaders TH
JOIN -- Pulls PatronID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails TDpat
ON (TH.TransactionID = TDpat.TransactionID AND TDpat.TransactionSubTypeID = 6)
JOIN -- Get the item's CollectionID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails TDcol
ON (TH.TransactionID = TDcol.TransactionID AND TDcol.TransactionSubTypeID = 61)
LEFT JOIN -- Brings in the Collections table to get Collection names
Polaris.Polaris.Collections C
ON (C.CollectionID = TDcol.numValue)
WHERE -- Checkouts
TH.TransactionTypeID = 6001
AND -- Adjust dates as necessary
TH.TranClientDate BETWEEN '1/1/2023' and '1/1/2024'
AND -- Adjust your organziations as necessary
TH.OrganizationID IN (6,7) -- Limit to Specific branches
GROUP BY
C.Name
ORDER BY
C.Name