This query gets a circulation count for items under a given bibliographic record, and it makes sure to include items that may have been deleted. The first part of the query pulls all the item record IDs ever created under your given bib record, and then uses that to get a circ count for each item.
-- Declare a few variables
DECLARE @BibRecordID INT;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
-- Set those variables - plug in your values here
SET @BibRecordID = 183323;
SET @StartDate = '2021-08-01 00:00:00.000';
SET @EndDate = '2021-12-31 23:59:59.999';
-- Let's pull all the items ever created for that bib and shove those into a table for cross-reference
DECLARE @CreatedItems TABLE
(ItemRecordID INT)
INSERT INTO @CreatedItems
SELECT
item.numValue AS [ItemRecordID]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails item on item.TransactionID = th.TransactionID
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails bib on th.TransactionID = bib.TransactionID and bib.TransactionSubTypeID = 39
WHERE
th.TransactionTypeID = 3008
AND
bib.numValue = @BibRecordID
AND
item.TransactionSubTypeID = 38
ORDER BY
[ItemRecordID]
/* ---------- Table Created - Let's Use It For Something ---------- */
SELECT
COUNT(th.TransactionID) AS [Item Circ Count],
td.numValue AS [ItemRecordID]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON th.TransactionID = td.TransactionID AND td.TransactionSubTypeID = 38
WHERE
th.TransactionTypeID = 6001
AND
th.TranClientDate BETWEEN @StartDate AND @EndDate
AND
td.numValue IN
(SELECT ItemRecordID FROM @CreatedItems)
GROUP BY
td.numValue
ORDER BY
[Item Circ Count] DESC
If you are using this query with data that was migrated from another system, then please note, the query will only provide data on item records that were created in Polaris AFTER your data migration.
This query should look for all current item records as well as any that were created historically (3008 transaction type id) on your system. The time period in the query only limits the output of counting the circulations; the query will look for any item record creations across the entire history of your PolarisTransactions database.
-- Declare a few variables
DECLARE @BibRecordID INT;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
-- Set those variables - plug in your values here
SET @BibRecordID = 3788219;
SET @StartDate = '2025-01-01 00:00:00.000';
SET @EndDate = '2025-12-31 23:59:59.999';
-- One deduped list of ItemRecordIDs for this bib:
-- (A) items currently attached to the bib in the item table
-- (B) items ever created for the bib via transactions
DECLARE @ItemIDs TABLE
(
ItemRecordID INT NOT NULL PRIMARY KEY
);
INSERT INTO @ItemIDs (ItemRecordID)
SELECT DISTINCT x.ItemRecordID
FROM
(
/* (A) Current items on the bib (from the item record table) */
SELECT
cir.ItemRecordID
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
WHERE
cir.AssociatedBibRecordID = @BibRecordID
UNION
/* (B) Items created for the bib (from transaction history) */
SELECT
item.numValue AS ItemRecordID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON item.TransactionID = th.TransactionID
AND item.TransactionSubTypeID = 38 -- ItemRecordID
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails bib WITH (NOLOCK)
ON bib.TransactionID = th.TransactionID
AND bib.TransactionSubTypeID = 39 -- BibRecordID
WHERE
th.TransactionTypeID = 3008 -- item create (per your original)
AND bib.numValue = @BibRecordID
) x;
-- (Optional) sanity check: see the deduped set
-- SELECT ItemRecordID FROM @ItemIDs ORDER BY ItemRecordID;
SELECT
COUNT(th.TransactionID) AS [Item Circ Count],
td.numValue AS [ItemRecordID]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK)
ON td.TransactionID = th.TransactionID
AND td.TransactionSubTypeID = 38 -- ItemRecordID
WHERE
th.TransactionTypeID = 6001 -- circ (per your original)
AND th.TranClientDate BETWEEN @StartDate AND @EndDate
AND td.numValue IN (SELECT ItemRecordID FROM @ItemIDs)
GROUP BY
td.numValue
ORDER BY
[Item Circ Count] DESC;
The original query and this query ONLY show circ transactions (6001) that happened in your system once you were live on Polaris. These may NOT match YTD totals on item or bib records, because those totals may have been migrated from your previous system. Check with the data migration team for specifics.
Hi @wesochuck - just a note that the Sierra Web Management Reports has a report that sums items and total circs associated with bib titles. If Innovative is moving that product’s functionality to a Sierra/Polaris analytics space then you should have this metric available to you.
Good to know. Yep, there are Polaris reports that do similar. It will be interesting to see if they release Vega analytics if they’ll be able to maintain that data for titles even if we switch between underlying ILS systems.
I think the tricky thing for this library was trying to determine whether the migrated data included the previous circs or not. Being able to run this type of query allowed them to confirm that the totals in their instancewere migrated.