Material Type Limits With Realistic Data

Clearinghouse Repository Polaris Repository
,
1

Material Type Limits With Realistic Data

Pull Material Type Loan Limit table and replicate the layout you see in Polaris SA. Output is limited to Patron Codes and Material Types actually used by the library.

SELECT
    o.Name AS "Organization",
    pc.PatronCodeID AS "PatronCodeID",
    pc.Description AS "Patron Code",
    mt.MaterialTypeID AS "MaterialTypeID",
    mt.Description AS "Material Type",
    mll.MaxItems AS "Maximum Item Limit",
    mll.MaxRequestItems AS "Hold Limit"

FROM
    Polaris.Polaris.MaterialLoanLimits mll WITH (NOLOCK)

JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = mll.OrganizationID
JOIN
    Polaris.Polaris.PatronCodes pc WITH (NOLOCK) ON pc.PatronCodeID = mll.PatronCodeID
JOIN
    Polaris.Polaris.MaterialTypes mt WITH (NOLOCK) ON mt.MaterialTypeID = mll.MaterialTypeID

-- LIMIT THE INITIAL PULL TO A GIVEN LIBRARY/LIBRARIES
WHERE
    mll.OrganizationID IN (1,2,3,4) -- CHANGE AS NEEDED. VALUES SHOULD MATCH THROUGHOUT THE QUERY.

-- LIMIT THE DATA TO ONLY THE PATRON CODES USED BY THAT LIBRARY
AND mll.PatronCodeID IN (
    SELECT DISTINCT(PatronCodeID) FROM Polaris.Polaris.Patrons WITH (NOLOCK)
    WHERE OrganizationID IN (1,2,3,4) -- CHANGE AS NEEDED. VALUES SHOULD MATCH THROUGHOUT THE QUERY.
)

-- LIMIT THE DATA TO ONLY THE MATERIAL TYPES USED BY THAT LIBRARY
AND mll.MaterialTypeID IN (
    SELECT DISTINCT(MaterialTypeID)
    FROM Polaris.Polaris.ItemRecords WITH (NOLOCK)
    WHERE OwningBranchID IN (1,2,3,4) -- CHANGE AS NEEDED. VALUES SHOULD MATCH THROUGHOUT THE QUERY.
)

ORDER BY
    o.Name,
    mll.PatronCodeID,
    mll.MaterialTypeID