Low Item Counts by Collection and Material Type
This query first pulls a table of low item counts based upon collection and material type. From that it’ll build a list of actual items that fall within the low item count table. This query is useful for finding items that may have the wrong collection and/or material type and the item record may need adjustment.
/* BEGIN TEMPORARY TABLE BUILD */
-- Set up a temporary table to hold our low item counts
DECLARE @LowItemCount TABLE
(
[CollectionID] INT,
[Collection] VARCHAR(250),
[MaterialTypeID] INT,
[MaterialType] VARCHAR(250),
[ItemCount] INT
)
-- Populate the temporary table
INSERT @LowItemCount
SELECT
cir.AssignedCollectionID AS [CollectionID],
c.Name AS [Collection],
cir.MaterialTypeID AS [MaterialTypeID],
mat.Description AS [MaterialType],
COUNT(cir.ItemRecordID) AS [ItemCount]
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = cir.MaterialTypeID
LEFT JOIN
Polaris.Polaris.Collections c WITH (NOLOCK) ON cir.AssignedCollectionID = c.CollectionID
WHERE
cir.ItemStatusID NOT IN (11) -- Filters out Withdrawn items, adjust as desired
AND
cir.AssignedBranchID IN (1,2,3,4) -- Adjust OrganizationIDs as desired
GROUP BY
cir.AssignedCollectionID,
c.Name,
cir.MaterialTypeID,
mat.[Description]
HAVING
COUNT(cir.ItemRecordID) <= 10 -- Adjust this equation to suit your max number of items that constitutes a low item count
/* END TEMPORARY TABLE BUILD */
-- Now pull individual item information based on the low item count table.
SELECT
cir.ItemRecordID AS [Item Record ID],
cir.Barcode AS [Item Barcode],
iss.Description AS [Circ Status],
c.Name AS [Collection],
mat.Description AS [Material Type],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author]
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = cir.MaterialTypeID
LEFT JOIN
Polaris.Polaris.Collections c with (nolock) ON cir.AssignedCollectionID = c.CollectionID
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON cir.AssociatedBibRecordID = br.BibliographicRecordID
INNER JOIN
Polaris.Polaris.ItemStatuses iss WITH (NOLOCK) ON iss.ItemStatusID = cir.ItemStatusID
JOIN
@LowItemCount lic ON (cir.AssignedCollectionID = lic.CollectionID) AND (cir.MaterialTypeID = lic.MaterialTypeID)
WHERE
cir.ItemStatusID NOT IN (11) -- Excludes withdrawn, adjust as desired
ORDER BY
c.Name,
mat.Description