SQL for patron fines

Looking to run SQL query to get patron fine info (from Sierra Listerv)

As David Noe reported, the descriptions you seek are based on fine.charge_code and are described in Sierra DNA.

This is one of those occasions when code descriptions are not stored in sibling tables that can be easily joined to. In this case, you must use a CASE statement to manually describe all possible descriptions for the codes – and hope that Innovative doesn’t add extra codes in the future. To test for this possibility, I use the ELSE clause to write out ‘unexpected code’ followed by the new code.

select

p.id,

p.home_library_code,

p.record_num,

p.mblock_code,

n.last_name,

n.first_name,

p.owed_amt,

f.assessed_gmt,

  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 (OVERDUEX)'

    WHEN f.charge_code = '5' THEN 'Lost'

    WHEN f.charge_code = '6' THEN 'Overdue Renewed'

    WHEN f.charge_code = '7' THEN 'Rental'

    WHEN f.charge_code = '8' THEN 'Rental Adjustment (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 Registration'

    ELSE 'unexpected code '||f.charge_code

  END                                           AS "Charge Type",

f.description,

f.item_charge_amt

from sierra_view.fine as f

join sierra_view.patron_view as p

on p.id = f.patron_record_id

join sierra_view.patron_record_fullname as n

on n.patron_record_id = p.id

;

As an example of a code which has a properly configured sibling table that describes the codes, we can use your fourth column, p.mblock_code. To see descriptions instead of codes in that column you could add a join to sierra_view.mblock_property_myuser and then display the ‘name’ column from that table. No ugly CASE statement required. Unfortunately, we have no alternative for charge_code

select

p.id,

p.home_library_code,

p.record_num,

m.name,

n.last_name,

n.first_name,

p.owed_amt,

f.assessed_gmt,

  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 (OVERDUEX)'

    WHEN f.charge_code = '5' THEN 'Lost'

    WHEN f.charge_code = '6' THEN 'Overdue Renewed'

    WHEN f.charge_code = '7' THEN 'Rental'

    WHEN f.charge_code = '8' THEN 'Rental Adjustment (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 Registration'

    ELSE 'unexpected code '||f.charge_code

  END                                           AS "Charge Type",

f.description,

f.item_charge_amt

from sierra_view.fine as f

join sierra_view.patron_view as p

on p.id = f.patron_record_id

join sierra_view.patron_record_fullname as n

on n.patron_record_id = p.id

JOIN sierra_view.mblock_property_myuser AS m

ON m.code = p.mblock_code

;

--

Brent Searle

Library Systems Manager

Langara College