Circ Count by Patron Statistical Class

Clearinghouse Repository Polaris Repository
, ,
1

This query produces a circ count for patron statistical classes along with the transacting branch and the patrons’ assigned branch. The original request (on the IUG Discord) called for UDF 4 to appear in the results but this can be modified/removed if needed.

SELECT
    tbranch.Abbreviation AS [Transacting Branch],
    patronstat.numValue AS [Patron Stat Code],
    pscc.Description AS [Patron Stat Class],
    pr.User4 AS [UDF 4],
    o.Abbreviation AS [Patron Assigned Branch],
    COUNT(DISTINCT th.TransactionID) AS [Circ Count]
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Pulls the transacting branch
    Polaris.Polaris.Organizations tbranch WITH (NOLOCK)
    ON (tbranch.OrganizationID = th.OrganizationID)
INNER JOIN -- Gets the patron statistical code ID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails patronstat WITH (NOLOCK)
    ON (th.TransactionID = patronstat.TransactionID AND patronstat.TransactionSubTypeID = 33)
INNER JOIN -- Gets the patron's assigned branch from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails patronbranch WITH (NOLOCK)
    ON (th.TransactionID = patronbranch.TransactionID AND patronbranch.TransactionSubTypeID = 123)
INNER JOIN -- Gets the PatronID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails patronid WITH (NOLOCK)
    ON (th.TransactionID = patronid.TransactionID AND patronid.TransactionSubTypeID = 6)
INNER JOIN -- Hooks up Organizations to the patron's assigned branch from TransactionDetails
    Polaris.Polaris.Organizations o WITH (NOLOCK)
    ON (o.OrganizationID = patronbranch.numValue)
LEFT JOIN -- Pulls in the Patron Stat Class information
    Polaris.Polaris.PatronStatClassCodes pscc WITH (NOLOCK)
    ON (pscc.StatisticalClassID = patronstat.numValue)
LEFT JOIN -- User4 (UDF4) lives in PatronRegistration
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
    ON (pr.PatronID = patronid.numValue)
WHERE -- Check outs
    th.TransactionTypeID = 6001
AND -- Adjust dates as desired
    th.TranClientDate BETWEEN '2024-03-01 00:00:00.000' AND '2024-03-14 23:59:59.999'
GROUP BY
    tbranch.Abbreviation,
    patronstat.numValue,
    pscc.Description,
    pr.User4,
    o.Abbreviation
ORDER BY
    COUNT(DISTINCT th.TransactionID) DESC