Please check the comments for updates to this query, especially the one that updates the logic to account for checking out multiple items under the same bibliographic record.
This query pulls the prices for items checked out at given branches in order to get You Saved data for those branches. However, this query excludes the renewals for those items to get only the initial You Saved data rather than bringing in prices for items renewed.
Note: Since the pricing data is pulled ItemRecordDetails, if an item is deleted, you won’t get that pricing information.
-- Create a table to pull in item renewals
CREATE TABLE #TempItemsRenewed (
TransactionID INT
);
-- Populate that table
INSERT INTO #TempItemsRenewed
SELECT
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
LEFT OUTER JOIN
PolarisTransactions.Polaris.TransactionDetails td (nolock)
ON (th.TransactionID = td.TransactionID)
WHERE -- Checkouts
th.TransactionTypeID = 6001
AND -- Renewals
td.TransactionSubTypeID = 124
AND -- Change orgs as desired but they should match the orgs below
th.OrganizationID IN (99,100,101)
AND -- Adjust dates as desired but they should match the dates below
th.TranClientDate BETWEEN '2019-01-01 00:00:00.000' AND '2019-12-31 23:59:59.999';
-- Data delivery
SELECT
SUM(ird.Price) AS [Total 'You Saved' Amount]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Pulls ItemRecordID
PolarisTransactions.Polaris.TransactionDetails itemid WITH (NOLOCK)
ON (th.TransactionID = itemid.TransactionID AND itemid.TransactionSubTypeID = 38)
INNER JOIN -- Bring in ItemRecordDetails
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK)
ON (ird.ItemRecordID = itemid.numValue)
WHERE -- Checkouts
th.TransactionTypeID = 6001
AND -- Adjust dates as desired but they should match the dates above
th.TranClientDate BETWEEN '2019-01-01 00:00:00.000' AND '2019-12-31 23:59:59.999'
AND -- Adjust orgs as desired but they should match the orgs above
th.OrganizationID IN (99,100,101)
AND -- Exclude the renewals
th.TransactionID NOT IN (SELECT TransactionID FROM #TempItemsRenewed)
-- Tidy up
DROP TABLE #TempItemsRenewed;
allows you to set a max price to weed out items with the wrong price
moves things to a CTE because that is the way LLMs like gemini like to output code
-- =================================================================
-- DEFINE YOUR VARIABLES HERE
-- =================================================================
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@MaxPrice MONEY;
-- Declare a table variable to hold the organization IDs
DECLARE @OrgIDs TABLE (ID INT);
-- Set the date range for the report
SET @StartDate = '2025-09-01 00:00:00.000';
SET @EndDate = '2025-09-30 23:59:59.999';
-- Set the MAXIMUM price for an item to be included in the sum
SET @MaxPrice = 1000.00;
-- INSERT the BRANCH Organization IDs you want to include in the report, this would be the branch where the CKO occured
INSERT INTO @OrgIDs (ID) VALUES
(7);
-- =================================================================
-- Use a CTE to identify renewal transactions first
;WITH RenewalTransactions AS
(
SELECT
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK)
ON th.TransactionID = td.TransactionID
WHERE
th.TransactionTypeID = 6001 -- TransactionTypeID: 6001 (Check out)
AND td.TransactionSubTypeID = 124 -- TransactionSubTypeID: 124 (Item was renewed)
AND th.OrganizationID IN (SELECT ID FROM @OrgIDs) -- Using @OrgIDs table variable
AND th.TranClientDate BETWEEN @StartDate AND @EndDate
)
-- Final SELECT statement to get the total sum
SELECT
SUM(ird.Price) AS [Total 'You Saved' Amount]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the ItemRecordID from the transaction
PolarisTransactions.Polaris.TransactionDetails itemid WITH (NOLOCK)
ON th.TransactionID = itemid.TransactionID AND itemid.TransactionSubTypeID = 38 -- TransactionSubTypeID: 38 (ItemRecordID)
INNER JOIN -- Get the price from the item record
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK)
ON itemid.numValue = ird.ItemRecordID
LEFT JOIN -- Join to our list of renewals to exclude them
RenewalTransactions RenewalT
ON th.TransactionID = RenewalT.TransactionID
WHERE
th.TransactionTypeID = 6001 -- TransactionTypeID: 6001 (Check out)
AND th.OrganizationID IN (SELECT ID FROM @OrgIDs) -- Using @OrgIDs table variable
AND th.TranClientDate BETWEEN @StartDate AND @EndDate
AND RenewalT.TransactionID IS NULL -- This excludes the renewals
AND ird.Price <= @MaxPrice;
I have an update for this query that reworks the logic to take into account the fact that patrons may not have renewed an item, but they have checked out another item that’s tied to a given bibliographic record. Given that the true basis of this query is to find out how much patrons have saved by using their library, we need to not only eliminate renewals (because they likely wouldn’t buy the same item twice) but we also have to eliminate instances where patrons checked out a different item associated with a bibliographic record they’ve already used.
In other words, say you have laptops available for checkout. The original query did not take into account that a patron would checkout Laptop A on one day and then get Laptop B on another and then they wind up with Laptop C next week. But with the original query, it’s like the patron bought three laptops. Needless to say, that’s sub-optimal. This update eliminates renewals and it pulls DISTINCT pairs of PatronID and BibliographicRecordID so that a patron checking out multiple items under a single bib record only counts once.
-- Let's use a CTE so hosted libraries can use the query
WITH PatronBibLevelCheckouts AS (
SELECT DISTINCT
patron.numValue AS PatronID,
cir.AssociatedBibRecordID AS BibliographicRecordID,
recent_price.Price
FROM
PolarisTransactions.Polaris.TransactionHeaders AS th WITH (NOLOCK)
INNER JOIN -- Get PatronID
PolarisTransactions.Polaris.TransactionDetails AS patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Get ItemRecordID
PolarisTransactions.Polaris.TransactionDetails AS item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
INNER JOIN -- Get BibliographicRecordID from CircItemRecords
Polaris.Polaris.CircItemRecords AS cir WITH (NOLOCK)
ON (item.numValue = cir.ItemRecordID)
LEFT JOIN -- Pull the renewals for later exclusion
PolarisTransactions.Polaris.TransactionDetails AS renewal WITH (NOLOCK)
ON (th.TransactionID = renewal.TransactionID AND renewal.TransactionSubTypeID = 124)
INNER JOIN -- Bring in the Patrons for later exclusion
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = patron.numValue)
LEFT JOIN -- Get price from most recent ItemRecordID for each BibliographicRecordID
(
SELECT
cir_sub.AssociatedBibRecordID,
ird.Price,
ROW_NUMBER() OVER (
PARTITION BY cir_sub.AssociatedBibRecordID
ORDER BY COALESCE(ird.ModificationDate, ird.CreationDate) DESC
) as rn
FROM
Polaris.Polaris.CircItemRecords AS cir_sub WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.ItemRecordDetails AS ird WITH (NOLOCK)
ON cir_sub.ItemRecordID = ird.ItemRecordID
WHERE
ird.Price IS NOT NULL
) AS recent_price
ON (cir.AssociatedBibRecordID = recent_price.AssociatedBibRecordID AND recent_price.rn = 1)
WHERE -- Checkouts
th.TransactionTypeID = 6001 -- Checkout transactions
AND -- Exclude renewals
renewal.TransactionID IS NULL
AND -- Limit to patrons at given branches
p.OrganizationID IN (99,100,101)
AND -- Adjust dates as desired
th.TranClientDate BETWEEN '2024-07-01 00:00:00.000' AND '2025-06-30 23:59:59.999'
)
SELECT
SUM(Price) AS [Total 'You Saved' Amount]
FROM
PatronBibLevelCheckouts