Find value of collection at a branch

Clearinghouse Repository Polaris Repository
1

Finds the value of a collection at a specific branch broken down by collection code. Only looks for items with a circ status of In. Does NOT include item records marked as eContent.

You can filter out accidentally high priced items. To find those high priced items, see: Find high priced item records

-- Variables
DECLARE @BranchID INT;
DECLARE @PriceLimit MONEY;
DECLARE @DefaultItemPrice MONEY;

SET @BranchID = 123;               -- Assigned branch
SET @PriceLimit = 500;             -- Exclude items priced above this
SET @DefaultItemPrice = 20.00;     -- Default price when item price is NULL

SELECT
    o.Name AS [AssignedBranch],
    ISNULL(c.Name, 'None') AS [Collection],
    ist.ItemStatusID,
    ist.Description AS [CircStatus],
    COUNT(DISTINCT cir.ItemRecordID) AS [TotalItems],
    SUM(COALESCE(ird.Price, @DefaultItemPrice)) AS [TotalPrice],
    CAST(
        SUM(COALESCE(ird.Price, @DefaultItemPrice))
        / NULLIF(COUNT(DISTINCT cir.ItemRecordID), 0)
        AS DECIMAL(18,2)
    ) AS [AveragePrice]
FROM polaris.polaris.CircItemRecords cir
JOIN polaris.polaris.ItemRecordDetails ird
    ON ird.ItemRecordID = cir.ItemRecordID
JOIN polaris.polaris.Organizations o
    ON o.OrganizationID = cir.AssignedBranchID
JOIN polaris.polaris.ItemStatuses ist
    ON ist.ItemStatusID = cir.ItemStatusID
LEFT JOIN polaris.polaris.Collections c
    ON c.CollectionID = cir.AssignedCollectionID
WHERE o.OrganizationID = @BranchID
  AND cir.ElectronicItem = 0
  AND cir.ItemStatusID = 1
  AND COALESCE(ird.Price, @DefaultItemPrice) <= @PriceLimit
GROUP BY
    o.Name,
    ISNULL(c.Name, 'None'),
    ist.ItemStatusID,
    ist.Description
ORDER BY
    o.Name,
    ISNULL(c.Name, 'None');
1 Like