Detailed Item List When Low Item Counts in Collection Codes
This query looks at your collection codes and checks to see if there are any collection codes with a low number of items. It’s set to operate at the level of a single branch. You can set this lower limit in the @CountLimit variable. After it gets a count of items in those collections it pulls a detailed list of those items so you can see if there are any issues. A low number of items in a collection code can indicate cataloguing errors or a need to move things around from one collection code to another.
-- Set your branch organization ID
DECLARE @CheckBranch INT = 31
-- Set your count limit - this will be inclusive so a count limit of 20 means 20 items or fewer
DECLARE @CountLimit INT = 20
/* --- Set up temp tables to hold data for cross reference --- */
CREATE TABLE #TempItemsCount (
AssignedCollectionID INT,
ItemCount INT
)
CREATE TABLE #TempItemsInfo (
ItemRecordID INT
)
/* --- Populate the TempItemsCount --- */
INSERT INTO #TempItemsCount
SELECT
AssignedCollectionID,
COUNT(*) AS ItemCount
FROM
Polaris.Polaris.CircItemRecords WITH (NOLOCK)
WHERE -- Limit by branch
AssignedBranchID = @CheckBranch
AND -- Excludes: Missing, Lost, Withdrawn, On-Order, Out-ILL
ItemStatusID NOT IN (3,7,10,11,13)
GROUP BY
AssignedCollectionID
/* --- Populate the TempItemsInfo --- */
INSERT INTO #TempItemsInfo
SELECT
ItemRecordID
FROM
Polaris.Polaris.CircItemRecords WITH (NOLOCK)
WHERE
AssignedCollectionID IN (
SELECT AssignedCollectionID
FROM #TempItemsCount
WHERE ItemCount <= @CountLimit
)
AND
AssignedBranchID = @CheckBranch
AND -- Excludes: Missing, Lost, Withdrawn, On-Order, Out-ILL
ItemStatusID NOT IN (3,7,10,11,13)
/* --- Deliver the data --- */
SELECT
o.Name AS [Assigned Branch],
c.Name AS [Collection],
cir.Barcode AS [Barcode],
ird.CallNumber AS [Call Number],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
(SELECT TOP 1 isbnString
FROM Polaris.Polaris.BibliographicISBNIndex bi WITH (NOLOCK)
WHERE bi.BibliographicRecordID = br.BibliographicRecordID) AS [Sort ISBN],
br.PublicationYear AS [Pub Year],
cir.LastCheckoutRenewDate AS [Last Checkout],
cir.LifetimeCircCount AS [Lifetime Circ]
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK)
ON (o.OrganizationID = cir.AssignedBranchID)
INNER JOIN
Polaris.Polaris.Collections c WITH (NOLOCK)
ON (c.CollectionID = cir.AssignedCollectionID)
INNER JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK)
ON (ird.ItemRecordID = cir.ItemRecordID)
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
WHERE
cir.ItemRecordID IN (
SELECT ItemRecordID
FROM #TempItemsInfo
)
ORDER BY
c.Name,
ird.CallNumber
-- Tidy up
DROP TABLE #TempItemsCount
DROP TABLE #TempItemsInfo