Material Types Actually In Use at a Library

Clearinghouse Repository Polaris Repository
,
1

Material Types Actually In Use at a Library

A library might have dozens of material types, but that doesn’t mean a branch or a library is using all of them. This query pulls a list of material types actually in use at a branch and presents them with an item count.

SELECT
    DISTINCT ir.MaterialTypeID AS "Material ID",
    mat.Description AS "Material Type",
    fc.[Description] AS "Fine Code",
    COUNT(ir.ItemRecordID) AS "Item Count",
    o.Name AS "Library"
FROM
    Polaris.Polaris.ItemRecords ir WITH (NOLOCK)

JOIN
    Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) on mat.MaterialTypeID = ir.MaterialTypeID
JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON ir.OwningBranchID = o.OrganizationID
JOIN
    Polaris.Polaris.FineCodes fc WITH (NOLOCK) ON fc.FineCodeID = ir.FineCodeID
WHERE
    ir.OwningBranchID IN (106,107,108)

GROUP BY
    o.Name,
    fc.[Description],
    ir.MaterialTypeID,
    mat.Description
    
ORDER BY
    mat.Description