Checkout Counts by CKO Location, Owning Location, and Collection

Clearinghouse Repository Polaris Repository
1

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 pull CollectionIDValue (from TransactionSubTypeID = 61) and OwningBranchIDValue (from TransactionSubTypeID = 125) from the PolarisTransactions.Polaris.TransactionDetails table for each transaction.
    • An important condition handled here is that the CollectionIDValue can be NULL, but the OwningBranchIDValue must be present for a transaction to be included.

Sample output:

image
image980×550 15.4 KB

/*
========================================================================================================================
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.