SQL to get past checkouts from Innovative ILS

We have turned off the last patron to checkout an item field, but we wanted to track down the person who borrowed a laptop charger (missing a part).  This info is stored in CIRC_TRANS table, generally 14 days worth, to see all: 

SELECT

  transaction_gmt

FROM

  sierra_view.circ_trans

ORDER BY

  1

LIMIT

  100

;

To locate one item by barcode, use this code:


SELECT
  circ_trans.transaction_gmt,
  circ_trans.patron_record_id,
  circ_trans.item_record_id,
  circ_trans.due_date_gmt,
  item_view.barcode,
  item_view.itype_code_num,
  patron_record_fullname.first_name,
  patron_record_fullname.last_name
FROM
  sierra_view.circ_trans,
  sierra_view.item_view,
  sierra_view.patron_view,
  sierra_view.patron_record_fullname
WHERE
  item_view.id = circ_trans.item_record_id AND
  patron_view.id = circ_trans.patron_record_id AND
  patron_record_fullname.patron_record_id = patron_view.id AND
  item_view.barcode = '11111103690302'
ORDER BY
  circ_trans.due_date_gmt ASC;

Barcode is CHAR field, so can use ::text to set the number to text, double quotes do not work  (PGadmin client)


To get daily stats on checkouts:  (via Andrew Hollingsworth)

Select Distinct

  count(*),

  Case sierra_view.circ_trans.op_code When 'o' Then 'Checkout'

    When 'n' Then 'Hold' When 'nb' Then 'Title Hold' When 'ni' Then 'Item Hold'

    When 'f' Then 'Hold Fufilled' When 'i' Then 'Checkin'

    When 'r' Then 'Renewal' When 'h' Then 'Recall Hold'

    When 'u' Then 'Use Count' When 'b' Then 'Booking'

    When 'hb' Then 'Hold Recall Bib'

    When 'hi' Then 'Hold Recall Item'

    When 'hv' Then 'Hold Recall Volume' End As transtype From

  sierra_view.circ_trans

  Where

  sierra_view.circ_trans.transaction_gmt Between '02/10/2015 00:00:00'

  And '02/11/2015 24:00:00'

Group By

  sierra_view.circ_trans.op_code

Order by

transtype asc