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;