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 CircItemDetails, 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;