Circ Count for All Items Under Bib Record

Clearinghouse Repository Polaris Repository
,
1

Circ Count for All Items Under Bib Record

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