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.

http://innovativeusers.org/index.php?option=com_content&view=article&id=174&appParams[method]=Full+Record&appParams[id]=373  

Regards,

Jim

 

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

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

pattern matching in Sierra Direct SQL

Using SQL pattern matching in Sierra, here is documentation:



The LIKE expression returns true if the string matches the supplied pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)
If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.
Some examples:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

LIKE pattern matching always covers the entire string. Therefore, to match a sequence anywhere within a string, the pattern must start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But backslash is still special to the string literal parser, so you still need two of them to match a backslash.)
It's also possible to select no escape character by writing ESCAPE ''. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern.
The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.
The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.