Expired Patron Count by Year

Clearinghouse Repository Polaris Repository
,
1

Expired Patron Count by Year

A simple little query that reports the number of patrons with expired cards in a given year. Years are presented with the count of patrons who expired in that year.

SELECT
    o.Name AS "Library",
    YEAR(pr.ExpirationDate) AS "Year of Expiration",
    COUNT(pr.PatronID) as "Patrons Expired"

FROM
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)

JOIN
    Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = pr.PatronID
JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = p.OrganizationID

WHERE
    pr.ExpirationDate BETWEEN '1980-01-01 00:00:00.000' AND '2021-03-19 23:59:59.999'
    
GROUP BY
    o.Name,
    YEAR(pr.ExpirationDate)

ORDER BY
    o.Name,
    YEAR(pr.ExpirationDate) ASC