Patron Demographic Data and Fines Information with Minimum Limit
Another useful query for fine-free transitions. This pulls a list of patrons by PatronID, Barcode, Branch/Library, Birthdate, Street Address, Postal Code, Last Activity Date, and total charges for a selectable Fee Reason. You can use it to create demographic information about patrons who owe fines.
/* Create a table to hold data for a secondary pull with joins */
DECLARE @TempPatronFines TABLE (
PatronID INT,
Reason VARCHAR(200),
Charge DECIMAL(20, 2)
)
INSERT @TempPatronFines
/* Pulls PatronID, the Fee Description, and totals the fees for each Fee Description */
SELECT
p.PatronID AS "PatronID",
frc.FeeDescription AS "Reason",
CAST(SUM(ac.OutstandingAmount) AS DECIMAL(20, 2)) AS "Charge"
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
INNER JOIN Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID)
LEFT JOIN Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (ac.FeeReasonCodeID = frc.FeeReasonCodeID)
WHERE
ac.OrganizationID IN (1,2,3,4) --LIMIT BY ORGANIZATION IF NEEDED
AND ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
TxnCodeID IN (1)
AND OutstandingAmount > 0
)
GROUP BY
p.PatronID,
frc.FeeDescription
/* Create a table to hold data for patrons over a given total balance */
DECLARE @TempPatronsBlocked TABLE (
PatronID INT
)
INSERT @TempPatronsBlocked
SELECT
PatronID
FROM
Polaris.Polaris.Patrons WITH (NOLOCK)
WHERE ChargesAmount > '10.00' -- SET THIS TO YOUR LOWER LIMIT FOR FINE BLOCKS
/* Now take that table and add some joins to get further patron info (without data duplication) */
SELECT
tpf.PatronID AS "Patron ID",
p.Barcode AS "Patron Barcode",
o.Name AS "Branch Library",
pr.Birthdate AS "Patron Birthdate",
addr.StreetOne,
pos.PostalCode AS "Postal Code",
p.LastActivityDate as "Last Activity Date",
tpf.Reason AS "Fee Reason",
tpf.Charge AS "Total Charges"
FROM
@TempPatronFines tpf
INNER JOIN Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = tpf.PatronID
INNER JOIN Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON tpf.PatronID = pr.PatronID
INNER JOIN Polaris.Polaris.Organizations o WITH (NOLOCK) ON p.OrganizationID = o.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
p.OrganizationID in (1,2,3,4) --LIMIT BY ORGANIZATION IF NEEDED
AND tpf.PatronID IN (
SELECT
PatronID
FROM
@TempPatronsBlocked
)
AND tpf.Reason = 'Overdue Item' -- LIMIT BY ONLY OVERDUES IF NEEDED
ORDER BY
tpf.PatronID,
--o.Name,
tpf.Reason
--tpf.Charge