This SQL will give you links to all the bib records in your III catalog: (Sierra Listserv, Craig Borman)
SELECT
--*
--p.best_title_norm,
--p.best_author_norm,
'http://your.iii.catalog.url.here/record=b' || m.record_num || '&' || 'title=' || p.best_title_norm || '&' || 'author=' || p.best_author_norm || '/'
FROM
sierra_view.record_metadata m
LEFT OUTER JOIN sierra_view.bib_record_property p
ON (m.id = p.bib_record_id)
WHERE
m.record_type_code = 'b'
AND
m.campus_code = ''
AND
m.deletion_date_gmt IS null
LIMIT 500
--OFFSET 1500000
;
https://github.com/joemontibello/iii-sql-queries
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
10/22/2015
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
Subscribe to:
Posts (Atom)