Patron Address Information Based On Fine Data
Another useful query for fine free proposals. This pulls patron barcode, total outstanding charges, patron code, patron branch, patron full name, patron address information, and primary email. We've used it for demographic data on patrons and their addresses to discover concentrations of patrons owing fines in a geographic location and see if any information can be derived from that.
SELECT
DISTINCT p.Barcode AS [Patron Barcode],
CAST(p.ChargesAmount AS DECIMAL (20,2)) AS [Outstanding Balance],
pc.Description AS [Patron Code],
o.name AS [Patron Branch],
pr.PatronFullName AS [Patron Full Name],
addr.StreetOne AS [Street One],
addr.StreetTwo AS [Street Two],
pos.City AS [City],
pos.State AS [State],
pos.PostalCode AS [ZIP Code],
pr.EmailAddress AS [Email]
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)
INNER JOIN
Polaris.Polaris.organizations o WITH (NOLOCK) ON (p.OrganizationID = o.OrganizationID)
INNER JOIN
Polaris.Polaris.PatronCodes pc WITH (NOLOCK) ON (p.PatronCodeID = pc.PatronCodeID)
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 -- Adust OrgIDs as needed
ac.OrganizationID IN (1,2,3,4,5)
AND -- Adjust charges amount as needed
p.ChargesAmount >= 20.00
AND
ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
TxnCodeID IN (1)
AND
OutstandingAmount > 0
)
ORDER BY
[Patron Branch] ASC,
[Patron Full Name] ASC