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:
- The item with the oldest (or
NULL
)LastCircTransactionDate
. - A total count of all items for that unique title at its single owning branch.
The Logic Overview (Using CTEs):
BibsOwnedBySingleBranch
CTE: Identifies titles exclusive to a single branch, respecting any material type exclusions you define, and notes that branch’sOwningBranchID
.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 usingCOUNT(...) OVER (PARTITION BY ...)
.RankedItems
CTE: Ranks items within each title, prioritizingNULL
LastCircTransactionDate
values as “oldest”, then by the actual oldest date, usingROW_NUMBER()
.- 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
theirMaterialTypeID
s into this@ExcludedMaterialTypes
table. - To include all material types (i.e., no exclusions): Simply leave the
@ExcludedMaterialTypes
table empty (do not run anyINSERT
statements into it).
The query’sWHERE
clauses then useNOT EXISTS
to check items against this list.
Sample output:
-- 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;