Navigation

10/31/2014

SierraDNA: SQL to get patrons who owe fines greater than $25

From Sierra Listserv, how to create query to get list of patrons who owe fines:



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.
-- ===================================================================================
-- 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 Leach
Automated Services Coordinator

No comments:

Post a Comment