Get Top 3 Titles by Collection

Clearinghouse Repository Polaris Repository
1

This query will pull your top circulating titles by collection. While it’s written to pull the top 3 titles, you can modify the number below as needed to pull the top 10, the top 20, or whatever you’d like.

-- Create a table to hold only the TransactionIDs for 6001 TransTypeIDs
CREATE TABLE #TempCheckoutTrans (
    TransactionID INT,
    OrganizationID INT,
    TranClientDate DATETIME
);

-- Populate that table
INSERT INTO #TempCheckoutTrans

SELECT
    TransactionID,
    OrganizationID,
    TranClientDate
FROM
    PolarisTransactions.Polaris.TransactionHeaders WITH (NOLOCK)
WHERE -- Checkouts
    TransactionTypeID = 6001
AND -- Adjust dates as needed
    TranClientDate BETWEEN '2024-01-01 00:00:00.000' AND '2024-12-31 23:59:59.999';

-- Create a table to hold onto data based on TransactionIDs in #TempCheckoutTrans
CREATE TABLE #TempCheckoutData (
    TransactionID INT,
    ItemRecordID INT,
    BibliographicRecordID INT,
    CollectionID INT
);

-- Populate that table
INSERT INTO #TempCheckoutData

SELECT
    th.TransactionID,
    item.numValue,
    cir.AssociatedBibRecordID,
    collect.numValue
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get ItemRecordID
    PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
    ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
RIGHT JOIN -- Get CollectionID
    PolarisTransactions.Polaris.TransactionDetails collect WITH (NOLOCK)
    ON (th.TransactionID = collect.TransactionID AND collect.TransactionSubTypeID = 61)
LEFT JOIN -- Get ItemRecordID for extant items
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (cir.ItemRecordID = item.numValue)
WHERE -- Only act upon the TransactionIDs in #TempCheckoutTrans
    th.TransactionID IN (SELECT TransactionID FROM #TempCheckoutTrans);

-- D A T A   D E L I V E R Y
WITH TotalCircCounts AS (
    SELECT
        tcd.BibliographicRecordID,
        br.BrowseTitle AS [Title],
        br.BrowseAuthor AS [Author],
        c.Name AS [Collection],
        COUNT(*) AS [TotalCircCount],
        ROW_NUMBER() OVER (
            PARTITION BY c.Name 
            ORDER BY COUNT(*) DESC
        ) AS RowNum
    FROM
        #TempCheckoutData tcd
    INNER JOIN
        Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
        ON (br.BibliographicRecordID = tcd.BibliographicRecordID)
    LEFT JOIN
        Polaris.Polaris.Collections c WITH (NOLOCK)
        ON (c.CollectionID = tcd.CollectionID)
    WHERE
        tcd.BibliographicRecordID IS NOT NULL
    AND
        tcd.CollectionID IS NOT NULL
    GROUP BY
        tcd.BibliographicRecordID,
        br.BrowseTitle,
        br.BrowseAuthor,
        c.Name
)
SELECT
    BibliographicRecordID,
    Title,
    Author,
    Collection,
    TotalCircCount AS [Total Circ Count]
FROM
    TotalCircCounts
WHERE -- Pull the top three rows based on TotalCircCount
    RowNum <= 3 -- Modify this number to get a longer or shorter list
ORDER BY
    Collection, RowNum;

-- Tidy up
DROP TABLE #TempCheckoutTrans;
DROP TABLE #TempCheckoutData;