Items with Anomalous Collection Codes and Material Types
This query looks for collections and material types with low item counts, which could indicate that the items have the wrong material type and/or collection designations in the item record. Using this query, you’ll get an entire list, all at once, that can be handed to someone to take out to the stacks to verify reality and see if any items need a correction.
-- Let's make sure we're using the right database.
USE Polaris;
-- Set up some variables
DECLARE @OrgID INT;
DECLARE @Counter INT = 1;
DECLARE @RowCounter INT;
DECLARE @AnomalousItemCount INT;
SET @OrgID = 10; -- Set this to be the OrganizationID of the libary you're interested in
SET @AnomalousItemCount = 10; -- Set this number to be your upper limit item count for the report
-- Create a table to hold our item counts and parameters
CREATE TABLE #TempItemColMatCounts (
TempID INT IDENTITY(1, 1) primary key,
CollectionID INT,
Collection NVARCHAR(250),
MaterialTypeID INT,
MaterialType NVARCHAR(250),
ItemCount INT
)
-- Create a table to deliver item level information
CREATE TABLE #TempItemAnomaliesList (
Barcode NVARCHAR(25),
Title NVARCHAR(500),
Author NVARCHAR(500),
CallNumber NVARCHAR(100),
MaterialType NVARCHAR(250),
Collection NVARCHAR(250)
)
/* ----- BEGIN POPULATE #TempItemColMatCounts TABLE ----- */
-- Populate the first table with data based on anomalous item counts
INSERT INTO #TempItemColMatCounts
SELECT
c.CollectionID,
c.Name,
mat.MaterialTypeID,
mat.Description,
COUNT(ir.ItemRecordID)
FROM
Polaris.Polaris.CircItemRecords ir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = ir.MaterialTypeID
LEFT JOIN
Polaris.Polaris.Collections c with (nolock) ON ir.AssignedCollectionID = c.CollectionID
WHERE
ir.ItemStatusID NOT IN (11) -- Filters out Withdrawn items
AND
ir.AssignedBranchID IN (@OrgID)
GROUP BY
c.CollectionID,
c.Name,
mat.MaterialTypeID,
mat.[Description]
HAVING
COUNT(ir.ItemRecordID) <= @AnomalousItemCount -- Only saves counts less than or equal to this number. Low counts indicate errors and anomalies. Change this number as needed.
/* ----- END POPULATE #TempItemColMatCounts TABLE ----- */
/* ----- BEGIN POPULATE #TempItemAnomaliesList TABLE ----- */
-- Get the number of rows in the #TempItemColMatCounts table to use as our count number
SET @RowCounter = (SELECT COUNT(*) FROM #TempItemColMatCounts)
-- To begin, @Counter = 1. Iterate through the population query over the rows in #TempColMatCounts.
WHILE @Counter <= @RowCounter
BEGIN
INSERT INTO #TempItemAnomaliesList
SELECT
cir.Barcode,
br.BrowseTitle,
br.BrowseAuthor,
ird.CallNumber,
mat.Description,
col.Name
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = cir.AssociatedBibRecordID
INNER JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK) ON ird.ItemRecordID = cir.ItemRecordID
INNER JOIN
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = cir.MaterialTypeID
INNER JOIN
Polaris.Polaris.Collections col WITH (NOLOCK) ON col.CollectionID = cir.AssignedCollectionID
WHERE
cir.AssignedCollectionID = (SELECT CollectionID FROM #TempItemColMatCounts WHERE TempID = @Counter)
AND
cir.MaterialTypeID = (SELECT MaterialTypeID FROM #TempItemColMatCounts WHERE TempID = @Counter)
AND
cir.AssignedBranchID IN (@OrgID)
AND
cir.ItemRecordID NOT IN (11) -- Exclude withdrawn items
SET @Counter = @Counter + 1
END
/* ----- END POPULATE #TempItemAnomaliesList TABLE ----- */
/* ----- BEGIN POPULATE #TempItemAnomaliesList TABLE - NULL COLLECTIONS ----- */
INSERT INTO #TempItemAnomaliesList
SELECT
cir.Barcode,
br.BrowseTitle,
br.BrowseAuthor,
ird.CallNumber,
mat.Description,
col.Name
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = cir.AssociatedBibRecordID
INNER JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK) ON ird.ItemRecordID = cir.ItemRecordID
INNER JOIN
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = cir.MaterialTypeID
LEFT JOIN
Polaris.Polaris.Collections col WITH (NOLOCK) ON col.CollectionID = cir.AssignedCollectionID
WHERE
mat.MaterialTypeID IN (SELECT MaterialTypeID FROM #TempItemColMatCounts WHERE CollectionID IS NULL)
AND
cir.AssignedBranchID IN (@OrgID)
AND
cir.ItemRecordID NOT IN (11) -- Exclude withdrawn items
AND
cir.AssignedCollectionID IS NULL
/* ----- END POPULATE #TempItemAnomaliesList TABLE - NULL COLLECTIONS ----- */
-- Get that lovely, delicious data
SELECT * FROM #TempItemAnomaliesList
ORDER BY Collection, MaterialType, Author, Title
-- Tidy up
DROP TABLE #TempItemColMatCounts
DROP TABLE #TempItemAnomaliesList