Navigation

9/16/2015

SQL for fines Purged from III ILS

From Sierra Listserv, 2015-09



Report for fine purges from a Sierra database, used the query below to produce a report in the same format as the Fines Paid function.


WITH
  charge_type(charge_type_code, charge_type_desc) AS
  (
    VALUES
      ( '1', 'Manual Charge' ),
      ( '2', 'Overdue' ),
      ( '3', 'Replacement' ),
      ( '4', 'Adjustment' ),
      ( '5', 'Lost Book' ),
      ( '6', 'Overdue Renewed' ),
      ( '7', 'Rental' ),
      ( '8', 'Rental Adjustment' ),
      ( '9', 'Debit' ),
      ( 'a', 'Notice' ),
      ( 'b', 'Credit Card' ),
      ( 'p', 'Program' )
  ),
  fine_creation_mode(fine_creation_mode_code, fine_creation_mode_desc) AS
  (
    VALUES
      ( 'a', 'Automatic' ),
      ( 'm', 'Manual' ),
      ( 'x', 'Adjustment' )
  ),
  payment_status(payment_status_code, payment_status_desc) AS
  (
    VALUES
      ( '0', 'No Payment' ),
      ( '1', 'Full Payment' ),
      ( '2', 'Partial Payment' ),
      ( '3', 'Waive' ),
      ( '4', 'Item Busy' ),
      ( '5', 'Will Pay' ),
      ( '6', 'Purge' ),
      ( '7', 'Credit' ),
      ( '8', 'Adjustment' )
  )
SELECT
    to_char(fp.fine_assessed_date_gmt, 'MM/DD/YYYY') AS "Date Assessed",
    TRIM(BOTH FROM pn.last_name || ', ' || pn.first_name || ' ' || pn.middle_name) AS "Patron Name",
    pm.record_num AS "Patron Record",
    fp.patron_record_metadata_id AS "Patron Unique ID",
    fp.invoice_num AS "Invoice",
    fp.item_charge_amt AS "Charge Amount",
    fp.processing_fee_amt AS "Processing Fee",
    fp.billing_fee_amt AS "Billing Fee",
    ct.charge_type_desc AS "Charge Type",
    fp.charge_location_code AS "Owning Location",
    to_char(fp.paid_date_gmt, 'MM/DD/YYYY') AS "Date Paid",
    fp.tty_num AS "Statistics Group",
    fp.last_paid_amt AS "Last Payment",
    fp.iii_user_name AS "Initials",
    fcm.fine_creation_mode_desc AS "Creation Mode",
    fp.description AS "Description",
    fp.paid_now_amt AS "Amount Paid",
    ps.payment_status_desc AS "Payment Status",
    COALESCE(NULLIF(fp.payment_type_code, ''), 'null') AS "Payment Type",
    fp.payment_note AS "Payment Note"
  FROM
    sierra_view.fines_paid AS fp
    LEFT JOIN sierra_view.record_metadata AS pm
      ON (
        pm.id = fp.patron_record_metadata_id
      )
    LEFT JOIN sierra_view.patron_record_fullname AS pn
      ON (
        pn.patron_record_id = fp.patron_record_metadata_id
        AND pn.display_order = 0
      )
    LEFT JOIN charge_type AS ct USING ( charge_type_code )
    LEFT JOIN fine_creation_mode AS fcm USING ( fine_creation_mode_code )
    LEFT JOIN payment_status AS ps USING ( payment_status_code )
;

Regards,
Jim Nicholls

-----------------



For the query to work with multiple fine purges and when your fines_paid has more than just fine purge data, you need to add the following where clause:



  WHERE
    fp.payment_status_code = '6'
    AND fp.paid_date_gmt >= DATE '2014-12-16' -- Purge date, YYYY-MM-DD

I’ve added the query to the IUG clearinghouse.


Regards,
Jim
 

9/10/2015

SQL to locate item records with no bib records


Here is SQL for III ILS to  locate item records with no bib records:


--  Locate  for item records with no links
--  John Boggs  - Sierra Listerv - 2015-09

SELECT
  *
FROM
  sierra_view.item_view
LEFT JOIN
  sierra_view.bib_record_item_record_link ON
  item_view.id = bib_record_item_record_link.item_record_id
WHERE
  bib_record_item_record_link.item_record_id is NULL

9/04/2015

Hourly usage stats from ILS



Question:  Is there any way to get specific item usage by hour?  (From Innovative ILS)



You can get hourly checkout information through a SQL query assuming that your circ_trans view is being populated with data (I'm not sure if that is done by default for everyone yet), the period of time that is covered by your circ_trans data, and the period you want to investigate.

Here is a simple example that will give an hourly breakdown of all checkouts for yesterday:

SELECT
   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",
   COUNT(*)                                              AS "Checkouts"
FROM
   sierra_view.circ_trans AS ct
WHERE
   ct.op_code = 'o'                                            -- checkout
   AND
   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday
GROUP BY 1
ORDER BY 1
;


This example adds a column for location code and limits the output to items located in Langara's media dept:

SELECT
   ct.item_location_code                                 AS "Location",
   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",
   COUNT(*)                                              AS "Checkouts"
FROM
   sierra_view.circ_trans AS ct
WHERE
   ct.op_code = 'o'                                            -- checkout
   AND
   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday
   AND
   ct.item_location_code LIKE 'lm%'                            -- from Langara Media
GROUP BY 1,2
ORDER BY 1,2
;


This example limits the query from Example 1 to a specific item record number. This may be closest to what you're aiming for:

SELECT
   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",
   COUNT(*)                                              AS "Checkouts"
FROM
   sierra_view.circ_trans AS ct
JOIN
   sierra_view.item_view AS iv
   ON
   iv.id = ct.item_record_id
WHERE
   ct.op_code = 'o'                                            -- checkout
   AND
   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday
   AND
   iv.record_num = '1211068'                                   -- specific item record
GROUP BY 1
ORDER BY 1
;


--
Brent Searle