Patrons Blocked by Birthdate and ZIP Code

Clearinghouse Repository Polaris Repository
,
1

Patrons Blocked by Birthdate and ZIP Code

This query looks for patrons who are blocked for excessive fines, within ZIP codes, and by birthdate. This one is good for fine free transitions as you can get information about children and teens with blocked accounts and cross reference that to ZIP codes and their economic statuses.

SELECT
    pos.PostalCode AS "ZIP Code",
    COUNT(DISTINCT ac.PatronID) AS "Patrons Blocked"
     
FROM
    Polaris.Polaris.PatronAccount ac WITH (NOLOCK)

INNER JOIN
    Polaris.Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID) 
INNER JOIN
    Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON (p.PatronID = pr.PatronID) 
INNER JOIN
    Polaris.Polaris.Organizations gov WITH (NOLOCK) ON (ac.OrganizationID = gov.OrganizationID)
LEFT JOIN
    Polaris.Polaris.PatronAddresses pa WITH (NOLOCK) ON (pr.PatronID = pa.PatronID AND pa.AddressTypeID = 2 )  
LEFT JOIN
    Polaris.Polaris.Addresses addr WITH (NOLOCK) ON (pa.AddressID = addr.AddressID)    
LEFT JOIN
    Polaris.Polaris.PostalCodes pos WITH (NOLOCK) ON (addr.PostalCodeID = pos.PostalCodeID)  

WHERE
    ac.OrganizationID IN (1,2,3,4,5,6,7,8,9,10)  -- Adjust with OrganizationID codes as needed
AND
    p.PatronCodeID IN (7,12,8,9,10,1,2,6,13,11)  -- Adjust with PatronCodeID as needed
AND
    pr.Birthdate BETWEEN '1800-02-01 00:00:00.000' AND '2005-02-04 23:59:59.999' -- Adjust for patron birthdate ranges.
AND
    p.ChargesAmount >= 10.00 -- Set this to be the fine limit where cards are blocked
AND
    ac.TxnID IN (
        SELECT
            TxnID 
        FROM
            Polaris.Polaris.PatronAccount WITH (NOLOCK)
        WHERE
            TxnCodeID IN (1)
        AND
            OutstandingAmount > 0) 
GROUP BY
    pos.PostalCode
ORDER BY
    "Patrons Blocked" DESC