Find unique titles for branches with the same parent org

Clearinghouse Repository Polaris Repository
1

This is an adaption of a script we had that has not been fully tested. Feel free to edit the script using the wiki function or add a reply with any comments. If you’re looking to see which branches have more than one copy of a title for example for weeding purposes, look at this thread: SQL for Duplicate Titles

This script identifies titles held by only one branch within the same parent organization. For these titles, the query shows:

  1. The item with the oldest (or NULL) LastCircTransactionDate.
  2. A total count of all items for that unique title at its single owning branch.

The Logic Overview (Using CTEs):

  1. BibsOwnedBySingleBranch CTE: Identifies titles exclusive to a single branch, respecting any material type exclusions you define, and notes that branch’s OwningBranchID.
  2. ItemsAndCountsForUniqueBibs CTE: Fetches all items for these titles from their specific owning branch, again respecting material type exclusions. It also calculates a total count of items per title at that branch using COUNT(...) OVER (PARTITION BY ...).
  3. RankedItems CTE: Ranks items within each title, prioritizing NULL LastCircTransactionDate values as “oldest”, then by the actual oldest date, using ROW_NUMBER().
  4. Final SELECT: Selects the top-ranked item (rn = 1) for each title, displaying its details along with the total item count.

Flexible Material Type Exclusions:

The query starts by declaring a table variable named @ExcludedMaterialTypes.

  • To exclude specific material types: INSERT their MaterialTypeIDs into this @ExcludedMaterialTypes table.
  • To include all material types (i.e., no exclusions): Simply leave the @ExcludedMaterialTypes table empty (do not run any INSERT statements into it).
    The query’s WHERE clauses then use NOT EXISTS to check items against this list.

Sample output:

image
image2156×550 32.3 KB

-- Declare and set the Parent OrganizationID
DECLARE @ParentOrgID INT;
SET @ParentOrgID = 8; -- Replace 8 with the actual OrganizationID of the parent organization

-- Declare the table variable for excluded MaterialTypeIDs
DECLARE @ExcludedMaterialTypes TABLE (MaterialTypeID INT PRIMARY KEY);

-- HOW TO USE THE EXCLUSION LIST:
-- Scenario 1: To exclude specific MaterialTypeIDs (e.g., 47 and 55)
--             Uncomment and modify the INSERT statement below.
-- INSERT INTO @ExcludedMaterialTypes (MaterialTypeID) VALUES (47), (55);

-- Scenario 2: To NOT exclude any MaterialTypeIDs (i.e., include all material types)
--             Leave the @ExcludedMaterialTypes table empty by keeping the INSERT statement commented out.


WITH BibsOwnedBySingleBranch AS (
    SELECT
        cir.AssociatedBibRecordID,
        MIN(cir.AssignedBranchID) AS OwningBranchID
    FROM
        polaris.Polaris.CircItemRecords cir
    INNER JOIN
        polaris.Polaris.Organizations branch_org ON cir.AssignedBranchID = branch_org.OrganizationID
    INNER JOIN
        polaris.Polaris.Organizations parent_org ON branch_org.ParentOrganizationID = parent_org.OrganizationID
    WHERE
        parent_org.OrganizationID = @ParentOrgID
        AND cir.RecordStatusID = 1
        -- MODIFIED: Check against the @ExcludedMaterialTypes table variable
        AND NOT EXISTS (SELECT 1 FROM @ExcludedMaterialTypes emt WHERE emt.MaterialTypeID = cir.MaterialTypeID)
    GROUP BY
        cir.AssociatedBibRecordID
    HAVING
        COUNT(DISTINCT cir.AssignedBranchID) = 1
),
ItemsAndCountsForUniqueBibs AS (
    SELECT
        cir.AssociatedBibRecordID,
        br.BrowseTitle,
        cir.ItemRecordID,
        cir.Barcode,
        cir.LastCircTransactionDate,
        bosb.OwningBranchID,
        parent_org_details.OrganizationID AS ActualParentOrgID,
        parent_org_details.Name AS ParentOrganizationNameString,
        COUNT(cir.ItemRecordID) OVER (PARTITION BY cir.AssociatedBibRecordID) AS TotalItemsAtBranchForBib
    FROM
        polaris.Polaris.CircItemRecords cir
    INNER JOIN
        BibsOwnedBySingleBranch bosb ON cir.AssociatedBibRecordID = bosb.AssociatedBibRecordID
                                      AND cir.AssignedBranchID = bosb.OwningBranchID
    INNER JOIN
        polaris.Polaris.BibliographicRecords br ON cir.AssociatedBibRecordID = br.BibliographicRecordID
    INNER JOIN
        polaris.Polaris.Organizations item_branch_org ON cir.AssignedBranchID = item_branch_org.OrganizationID
    INNER JOIN
        polaris.Polaris.Organizations parent_org_details ON item_branch_org.ParentOrganizationID = parent_org_details.OrganizationID
    WHERE
        parent_org_details.OrganizationID = @ParentOrgID
        AND cir.RecordStatusID = 1
        -- MODIFIED: Check against the @ExcludedMaterialTypes table variable
        AND NOT EXISTS (SELECT 1 FROM @ExcludedMaterialTypes emt WHERE emt.MaterialTypeID = cir.MaterialTypeID)
),
RankedItems AS (
    SELECT
        iac.*,
        ROW_NUMBER() OVER (
            PARTITION BY iac.AssociatedBibRecordID
            ORDER BY
                CASE WHEN iac.LastCircTransactionDate IS NULL THEN 0 ELSE 1 END ASC,
                iac.LastCircTransactionDate ASC,
                iac.ItemRecordID ASC
        ) AS rn
    FROM
        ItemsAndCountsForUniqueBibs iac
)
SELECT
    ri.BrowseTitle,
    ri.AssociatedBibRecordID,
    ri.Barcode,
    ri.LastCircTransactionDate,
    branch_details.Name AS ItemBranchName,
    ri.ParentOrganizationNameString AS ParentOrganizationName,
    ri.TotalItemsAtBranchForBib
FROM
    RankedItems ri
INNER JOIN
    polaris.Polaris.Organizations branch_details ON ri.OwningBranchID = branch_details.OrganizationID
WHERE
    ri.rn = 1
    AND ri.ActualParentOrgID = @ParentOrgID
ORDER BY
    ri.BrowseTitle,
    ri.Barcode;
1 Like
2

Hi @wesochuck. Maybe I’m missing something, but wouldn’t the window function DENSE_RANK() be a more appropriate choice than ROW_NUMBER() in the RankedItems CTE?  Or is the idea to avoid ties?

3

It is correct that we don’t want ties, and of course a downside to vibe coding is that you’re more likely to get the most commonly used thing out there, and I think ROW_NUMBER() has more frequent appearances out in the wild.

1 Like
4

Given my fondness for my Luddite flip phone, it won’t surprise you to learn that I have not dabbled in AI SQL generation (or AI anything else).

One day the SkyNet chatbot will respond with ;DROP DATABASE [Humanity]; :nerd_face: