Hi Barbara:
David Jones is correct. You'll need to remove the check digit from your patron name to find a match in the database because the record number is stored internally without the check digit.
I have adapted an existing query of mine to get part way to what you are looking for. The query limits to patrons who owe $25 or more and who do not have manual block 'c'. I haven't limited to the age of the fines or fees. You'll have to fine tune it to get exactly what you're after. I included the check digit on the patron number thanks to some code provided a few months back by Jim Nicholls at University of Sydney.
The end result is 23 columns of data pertaining to the patron and the patron's outstanding fines and fees. Remove what you don't require. The data can be easily exported to Excel and then filtered to a specific patron.
David Jones is correct. You'll need to remove the check digit from your patron name to find a match in the database because the record number is stored internally without the check digit.
I have adapted an existing query of mine to get part way to what you are looking for. The query limits to patrons who owe $25 or more and who do not have manual block 'c'. I haven't limited to the age of the fines or fees. You'll have to fine tune it to get exactly what you're after. I included the check digit on the patron number thanks to some code provided a few months back by Jim Nicholls at University of Sydney.
The end result is 23 columns of data pertaining to the patron and the patron's outstanding fines and fees. Remove what you don't require. The data can be easily exported to Excel and then filtered to a specific patron.
-- ===================================================================================
-- Fines details for patrons Owing $25.00 or more
-- and without manual block 'c'
-- Brent Searle. Langara College. 2014-10-30
-- Check digit calculation via Jim Nicholls, University of Sydney
-- ===================================================================================
SELECT
p.record_type_code||p.record_num||
COALESCE( -- Check digit calculation
CAST(
NULLIF(
(
( p.record_num % 10 ) * 2 +
( p.record_num / 10 % 10 ) * 3 +
( p.record_num / 100 % 10 ) * 4 +
( p.record_num / 1000 % 10 ) * 5 +
( p.record_num / 10000 % 10 ) * 6 +
( p.record_num / 100000 % 10 ) * 7 +
( p.record_num / 1000000 ) * 8
) % 11,
10
)
AS CHAR(1)
),
'x'
) AS "Record Number",
pnam.last_name||
CASE
WHEN pnam.first_name IS NOT NULL THEN ', '||pnam.first_name
ELSE NULL
END||
CASE
when pnam.middle_name IS NOT NULL THEN ' '||pnam.middle_name
ELSE NULL
END AS "Patron Name",
p.barcode AS "Patron Barcode",
p.home_library_code AS "Home Library",
p.ptype_code AS "Ptype code",
ptnam.name AS "Patron Type",
f.invoice_num AS "Invoice",
CASE
WHEN f.charge_code = '1' THEN 'MANUAL'
WHEN f.charge_code = '2' THEN 'Overdue'
WHEN f.charge_code = '3' THEN 'Replacement'
WHEN f.charge_code = '4' THEN 'OverdueX'
WHEN f.charge_code = '5' THEN 'Lost'
WHEN f.charge_code = '6' THEN 'Overdue Renewal'
WHEN f.charge_code = '7' THEN 'Rental'
WHEN f.charge_code = '8' THEN 'RentalX'
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 Reg'
ELSE 'unexpected code '||f.charge_code
END AS "Type",
f.description AS "Reason",
f.title AS "Title",
i.barcode AS "Item Barcode",
f.charge_location_code AS "Item Location",
to_char(f.assessed_gmt,'YYYY-MM-DD HH:MI AM') AS "Date Assessed",
to_char(f.checkout_gmt,'YYYY-MM-DD HH:MI AM') AS "Date Checked Out",
to_char(f.due_gmt,'YYYY-MM-DD HH:MIAM') AS "Date Due",
to_char(f.returned_gmt,'YYYY-MM-DD HH:MI AM') AS "Date Returned",
to_char(f.item_charge_amt,'$99G990D99') AS "Item Charge",
to_char(f.processing_fee_amt,'$99G990D99') AS "Processing Fee",
to_char(f.billing_fee_amt,'$99G990D99') AS "Billing Fee",
to_char(f.item_charge_amt+
f.processing_fee_amt+
f.billing_fee_amt,'$99G990D99') AS "Total",
to_char(f.paid_amt,'$99G990D99') AS "Amount Paid",
to_char(f.item_charge_amt+
f.processing_fee_amt+
f.billing_fee_amt-
f.paid_amt,'$99G990D99') AS "Amount Due"
FROM
sierra_view.patron_view AS p
JOIN -- join to patron full name
sierra_view.patron_record_fullname AS pnam
ON
pnam.patron_record_id = p.id
JOIN -- join to ptype description
sierra_view.ptype_property_myuser AS ptnam
ON
ptnam.value = p.ptype_code
JOIN -- join to fine data
sierra_view.fine AS f
ON
f.patron_record_id = p.id
JOIN -- join for item barcode
sierra_view.item_view AS i
ON
i.id = f.item_record_metadata_id
WHERE
p.owed_amt >= 25 -- patron owes $25.00 or more
AND
p.mblock_code != 'c' -- patron doesn't have manual block 'c'
ORDER BY
2,14
;
--
Brent Searle
Library Systems Manager
Langara College
Vancouver BC
On
2014-10-30 2:23 PM, Leach, Barbara wrote:
I’m at the very beginning of trying to create a sql query to
get a list of patrons who owe $25 or more in fines, they don’t have manual
block ‘c’, their fine was assessed more than a month ago, and they don’t have a
fee assessed more recently than a month ago. I can use Create Lists to
get the list, but the fields I want to export aren’t available for
export. So, I’m trying to re-create this with sql, so I can get the
fields I want exported.
However, I can’t even get
this simple query to return any data. What am I doing wrong with this
simple query?
SELECT
patron_view.record_num
AS "Record Number",
patron_view.barcode
AS "Barcode",
patron_view.owed_amt
AS "Amount Owed",
patron_view.home_library_code
AS "Home Library"
FROM
sierra_view.patron_view
WHERE
patron_view.record_num
= '10068260';
When I run this, the output
pane contains 0 rows. I know that’s a valid patron record number, because
it is a record that was included in my Create Lists results.
I need specific data about
the fines, such as the date they were assessed, and that’s not available in
Create Lists export fields.
If anyone already has a query
that is similar to what I’m trying to create, would you mind sharing?
Thank you.
Barbara
Barbara
Barbara
Leach
Automated
Services Coordinator
