SQL script to retrieve a count of distinct checkout transactions. It groups these counts by the CKO (Checkout) Location Name, the Owning Location Name, and the Collection Name, all within a specified date range. This was based on a Discord request and there is a bit of vibe coding at play, so make sure to double-check the results.
Here’s a brief overview of how it works:
- Core Task: Counts unique checkout transactions (
TransactionTypeID = 6001
). - Date Filtering: Filters transactions based on
TranClientDate
. - Attribute Gathering (CTE): It uses a Common Table Expression (CTE) named
TransactionDetailAttributes
to efficiently pullCollectionIDValue
(fromTransactionSubTypeID = 61
) andOwningBranchIDValue
(fromTransactionSubTypeID = 125
) from thePolarisTransactions.Polaris.TransactionDetails
table for each transaction.- An important condition handled here is that the
CollectionIDValue
can be NULL, but theOwningBranchIDValue
must be present for a transaction to be included.
- An important condition handled here is that the
Sample output:
/*
========================================================================================================================
Retrieve the count of distinct checkout transactions, grouped by CKO Location Name, Collection Name,
and Owning Location Name, within a specified date range.
This version uses a Common Table Expression (CTE) to consolidate fetching attributes from TransactionDetails.
Updated to reflect that CollectionID (from SubTypeID 61) can be NULL,
while OwningBranchID (from SubTypeID 125) must be present.
Aliases for CKO and Owning locations, and their corresponding Organization table joins, have been clarified.
========================================================================================================================
*/
WITH TransactionDetailAttributes AS (
-- This CTE retrieves the CollectionID and OwningBranchID from the TransactionDetails table.
-- It pivots rows to columns for a given TransactionID.
SELECT
TransactionID,
MAX(CASE WHEN TransactionSubTypeID = 61 THEN numValue END) AS CollectionIDValue, -- Extracts CollectionID if TransactionSubTypeID is 61 (can be NULL)
MAX(CASE WHEN TransactionSubTypeID = 125 THEN numValue END) AS OwningBranchIDValue -- Extracts OwningBranchID if TransactionSubTypeID is 125 (must be present)
FROM
PolarisTransactions.Polaris.TransactionDetails
WHERE
TransactionSubTypeID IN (61, 125) -- Filter for relevant subtypes early
GROUP BY
TransactionID
HAVING
-- Ensure that the OwningBranchIDValue (from SubTypeID 125) was found for the TransactionID.
-- CollectionIDValue (from SubTypeID 61) can be NULL.
MAX(CASE WHEN TransactionSubTypeID = 125 THEN numValue END) IS NOT NULL
)
SELECT
CKO_Org.Name AS [CKOLocationName], -- The name of the CKO (Checkout Organization) location where the transaction occurred.
C.Name AS [collection.name], -- The name of the collection to which the checked-out item belongs, aliased as collection.name. (Will be NULL if CollectionIDValue is NULL)
Owning_Org.Name AS [OwningLocationName], -- The name of the Owning Location/Branch organization.
COUNT(DISTINCT TH.TransactionID) AS CirculationCount -- The total number of unique checkout transactions.
FROM
PolarisTransactions.Polaris.TransactionHeaders TH
/*
Join the CTE to get CollectionID and OwningBranchID for each transaction.
This replaces the two separate JOINs to PolarisTransactions.Polaris.TransactionDetails.
*/
JOIN
TransactionDetailAttributes TDA
ON TH.TransactionID = TDA.TransactionID
/*
Left Join Collections table to translate CollectionIDValue (from TDA) into a Collection Name.
A LEFT JOIN is used because TDA.CollectionIDValue can be NULL (if no SubTypeID 61 was present or its numValue was NULL).
If TDA.CollectionIDValue is NULL, C.Name will also be NULL.
*/
LEFT JOIN
Polaris.Polaris.Collections C
ON C.CollectionID = TDA.CollectionIDValue
/*
Join Organizations table to get the CKO Location Name based on the OrganizationID from TransactionHeaders.
This is aliased as 'CKO_Org'.
*/
JOIN
Polaris.Polaris.Organizations CKO_Org -- Alias for the CKO Location's Organization table
ON CKO_Org.OrganizationID = TH.OrganizationID
/*
Join Organizations table AGAIN to get the Owning Location Name.
We use the TDA.OwningBranchIDValue (which is the Owning Branch's OrganizationID)
to link to the Organizations table. This is aliased as 'Owning_Org'.
Since OwningBranchIDValue is guaranteed by the CTE's HAVING clause, an INNER JOIN is appropriate here,
assuming all OwningBranchIDValues have a match in the Organizations table.
Consider a LEFT JOIN if an OwningBranchIDValue might not always exist in the Organizations table.
*/
JOIN
Polaris.Polaris.Organizations Owning_Org -- Alias for the Owning Location's Organization table
ON Owning_Org.OrganizationID = TDA.OwningBranchIDValue
WHERE
-- Filter for checkout transactions.
TH.TransactionTypeID = 6001 -- Assumed to be the ID for checkout transactions.
AND
-- Filter transactions within the specified date range.
TH.TranClientDate BETWEEN '2023-01-01' AND '2024-01-01' -- Standardized date format.
GROUP BY
CKO_Org.Name, -- Group results by CKO Location Name.
Owning_Org.Name, -- Then, by Owning Location Name.
C.Name -- Then, group results by Collection Name. (Will group NULLs for collection.name together)
ORDER BY
CKO_Org.Name, -- Sort results by CKO Location Name.
Owning_Org.Name, -- Then, by Owning Location Name.
C.Name -- Then, sort by Collection Name.