ADDING
PATRON NAMES TO OUTPUT
First, let's add patron names to your existing query. You start by adding a JOIN to sierra_view.patron_record_fullname. You than add a name column to your output. The trick here is that Sierra stores names as first_name, middle_name, and last_name in three separate columns. In order to output the full name, you need to concatenate the pieces together.
In the following query, you'll find the concatenated name in LINE 4. The additional JOIN is in LINES 21-24.
First, let's add patron names to your existing query. You start by adding a JOIN to sierra_view.patron_record_fullname. You than add a name column to your output. The trick here is that Sierra stores names as first_name, middle_name, and last_name in three separate columns. In order to output the full name, you need to concatenate the pieces together.
In the following query, you'll find the concatenated name in LINE 4. The additional JOIN is in LINES 21-24.
select
p.home_library_code,
p.record_num,
pn.last_name||', '||pn.first_name||' '||pn.middle_name,
p.barcode,
f.assessed_gmt,
f.paid_gmt,
f.charge_code,
f.item_charge_amt,
f.billing_fee_amt,
f.processing_fee_amt,
f.paid_amt,
f.title,
f.description
from sierra_view.fine as f
JOIN -- to patron view
sierra_view.patron_view AS p
ON
p.id = f.patron_record_id
and assessed_gmt <= '2014-03-31 00:00:00'
JOIN -- to patron name
sierra_view.patron_record_fullname AS pn
ON
pn.patron_record_id = p.id
;
ADDING A NESTED QUERY
In order to limit your output to only those patrons who have fines and fees that were assessed on or before 2013-03-31 and which total $25 or more, I think you'll need to do a nested query. The following query will give you a list of patron record ids that match the desired criteria -
SELECT
patron_record_id
FROM
sierra_view.fine
WHERE
DATE(assessed_gmt) <= '2014-03-31'
GROUP BY
1
HAVING
SUM(item_charge_amt+processing_fee_amt+billing_fee_amt-paid_amt) >= '25'
;
You then nest that query into the larger query. Output from the larger query will be limited to only patrons whose patron record IDs are found in the nested query's result set. Note the WHERE clause (LINES 25-37) -
select
p.home_library_code,
p.record_num,
pn.last_name||', '||pn.first_name||' '||pn.middle_name,
p.barcode,
f.assessed_gmt,
f.paid_gmt,
f.charge_code,
f.item_charge_amt,
f.billing_fee_amt,
f.processing_fee_amt,
f.paid_amt,
f.title,
f.description
from sierra_view.fine as f
JOIN -- to patron view
sierra_view.patron_view AS p
ON
p.id = f.patron_record_id
and assessed_gmt <= '2014-03-31 00:00:00'
JOIN -- to patron name
sierra_view.patron_record_fullname AS pn
ON
pn.patron_record_id = p.id
WHERE
f.patron_record_id in
(
SELECT
patron_record_id
FROM
sierra_view.fine
WHERE
DATE(assessed_gmt) <= '2014-03-31'
GROUP BY 1
HAVING
SUM(item_charge_amt+processing_fee_amt+billing_fee_amt-paid_amt) >= '25'
)
;
OUTPUT AND CODE FORMAT
I would tend to want to format my output for dates and currency and I would want to add textual descriptions for charge_code and home_library_code. I would also tidy up my query code in some way that makes it easier for me to read later. Here are my suggestions. Make use of whatever works for you.
- I replaced the bare home library code with a branch name. I got this by adding another JOIN to sierra_view.location_myuser
- I used the DATE() function on f.assessed_gmt to quickly strip off the transaction time and timezone, leaving only the date
- I added a CASE statement to provide text for charge_code. There is no view that holds these descriptors (as far as I can tell). I got them from Sierra DNA and have to hope that they don't change over time.
- I formatted the currency fields to use only two decimal places and to show the dollar sign
- I added a "Balance Owing" column wherein I add the three charge columns and subtract the amount already paid
-- ===============================================================================
-- Patrons with $25 or more in fines assessed on or before 2014-03-31
-- Detail of fines owing
-- Brent Searle. Langara College. 2015-02-06
-- ===============================================================================
SELECT
ln.name AS "Home Library",
p.record_num AS "Patron Number",
TRIM(pn.last_name||', '||
pn.first_name||' '||
pn.middle_name) AS "Patron Name",
p.barcode AS "Patron Barcode",
DATE(f.assessed_gmt) AS "Date Assessed",
CASE
WHEN f.charge_code = '1' THEN 'manual charge'
WHEN f.charge_code = '2' THEN 'overdue'
WHEN f.charge_code = '3' THEN 'replacement'
WHEN f.charge_code = '4' THEN 'adjustment'
WHEN f.charge_code = '5' THEN 'lost item'
WHEN f.charge_code = '6' THEN 'overdue renewed'
WHEN f.charge_code = '7' THEN 'rental'
WHEN f.charge_code = '8' THEN 'rental adjustment'
WHEN f.charge_code = '9' THEN 'debit'
WHEN f.charge_code = 'a' THEN 'notice'
WHEN f.charge_code = 'b' THEN 'credit card'
WHEN f.charge_code = 'p' THEN 'program'
ELSE 'unexpected charge code - '||f.charge_code
END AS "Charge Type",
TO_CHAR(f.item_charge_amt,'$990.99') AS "Amount Charged",
TO_CHAR(f.billing_fee_amt,'$990.99') AS "Billing Fee",
TO_CHAR(f.processing_fee_amt,'$990.99') AS "Processing Fee",
TO_CHAR(f.paid_amt,'$990.99') AS "Amount Paid",
TO_CHAR(f.item_charge_amt +
f.billing_fee_amt +
f.processing_fee_amt -
f.paid_amt,'$990.99') AS "Balance Owing",
f.title AS "Title",
f.description AS "Fine Description"
FROM
sierra_view.fine as f
JOIN -- to patron view
sierra_view.patron_view AS p
ON
p.id = f.patron_record_id
JOIN
sierra_view.patron_record_fullname AS pn
ON
pn.patron_record_id = p.id
JOIN
sierra_view.location_myuser AS ln
ON
ln.code = p.home_library_code
WHERE
DATE(f.assessed_gmt) <= '2014-03-31' -- specific date may need editing
AND
p.owed_amt >= '25'
AND
f.patron_record_id IN
(
SELECT
patron_record_id
FROM
sierra_view.fine
WHERE
DATE(assessed_gmt) <= '2014-03-31' -- specific date may need editing
GROUP BY
1
HAVING
SUM(item_charge_amt+processing_fee_amt+billing_fee_amt-paid_amt) >= '25'
)
ORDER BY
1,2,4
;
You should be able to export the output to MS Excel and, by way of the SUBTOTAL function, subtotal on each change of patron number.
--
Brent Searle
Library Systems Manager
Langara College
Vancouver BC
On
2015-02-03 1:48 PM, Leach, Barbara wrote:
I’m hoping maybe one of you SQL gurus can help me with this
SQL for Collection Agency candidates. The following query lists fines
assessed prior to a certain date (I used 3/31/14 to keep the results list
small. We went live on Sierra on 1/23/14)
Now, what would be really helpful is if it would sum all of the
fines, and only include in the output patrons who owed $25 or more prior to the
specified date. This is way beyond my sql abilities.
Also, I’d like to include the patron name, but I didn’t know how to add a second join to the patron name table. (I tried, but it didn’t work. )
select
p.home_library_code,
p.record_num,
p.barcode,
f.assessed_gmt,
f.paid_gmt,
f.charge_code,
f.item_charge_amt,
f.billing_fee_amt,
f.processing_fee_amt,
f.paid_amt,
f.title,
f.description
from sierra_view.fine as f
JOIN -- to patron view
sierra_view.patron_view AS p
ON
p.id = f.patron_record_id
and assessed_gmt <= '2014-03-31 00:00:00'
;
Thanks so much for your help! We just can’t figure out
a way to get Sierra to work to send patrons with fines to Collection Agency, so
it doesn’t include current fines. Any way we imagine involves a lot of
manual staff work, that we’re just not used to having to do (our previous ILS
did it for us perfectly)
III OPAC
III OPAC
