NOW() - (SELECT MIN(value) FROM (
SELECT value::INTERVAL FROM actor.org_unit_ancestor_setting(
'circ.staff.max_visible_event_age',
COALESCE(targ_circ.circ_lib, targ_ahr.pickup_lib)
) UNION
SELECT '1000 YEARS'::INTERVAL AS value
) ous)
]]>
SELECT * FROM metabib.author_field_entry
UNION ALL
SELECT * FROM metabib.keyword_field_entry
UNION ALL
SELECT * FROM metabib.identifier_field_entry
UNION ALL
SELECT * FROM metabib.title_field_entry
UNION ALL
SELECT * FROM metabib.subject_field_entry
UNION ALL
SELECT * FROM metabib.series_field_entry
SELECT usr,
SUM(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN 1
ELSE 0
END
) AS out,
SUM(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN 1
ELSE 0
END
) AS overdue,
SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN 1 ELSE 0 END) AS lost,
SUM( CASE WHEN stop_fines = 'CLAIMSRETURNED' THEN 1 ELSE 0 END) AS claims_returned,
SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN 1 ELSE 0 END) AS long_overdue
FROM action.circulation
WHERE checkin_time IS NULL
GROUP BY 1
SELECT usr,
STRING_AGG(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN id::TEXT
ELSE '0'
END
,',') AS out,
STRING_AGG(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN id::TEXT
ELSE '0'
END
,',') AS overdue,
STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id::TEXT ELSE '0' END,',') AS lost,
STRING_AGG( CASE WHEN stop_fines = 'CLAIMSRETURNED' THEN id::TEXT ELSE '0' END,',') AS claims_returned,
STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id::TEXT ELSE '0' END,',') AS long_overdue
FROM action.circulation
WHERE checkin_time IS NULL
GROUP BY 1
ahr.requestor) AS is_staff_hold,
ahcm_1.copy_count AS potential_copies
FROM action.hold_request ahr
JOIN asset.copy acp ON (acp.id = ahr.current_copy)
JOIN asset.call_number acn ON (acp.call_number = acn.id)
JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
JOIN actor.usr au ON (au.id = ahr.usr)
JOIN (
SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
FROM asset.copy_location
) acpl_ordered ON (acpl_ordered.id = acp.location)
LEFT JOIN actor.usr_standing_penalty ausp
ON (ahr.usr = ausp.usr AND (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
LEFT JOIN config.standing_penalty csp
ON (
csp.id = ausp.standing_penalty AND
csp.block_list LIKE '%CAPTURE%' AND (
(csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
(csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
)
)
)
JOIN (
SELECT COUNT(target_copy) AS copy_count, hold
FROM action.hold_copy_map
GROUP BY 2
) ahcm_1 ON (ahcm_1.hold = ahr.id)
LEFT JOIN serial.issuance siss
ON (ahr.hold_type = 'I' AND siss.id = ahr.target)
LEFT JOIN asset.copy_location_order acplo
ON (acp.location = acplo.location AND
acp.circ_lib = acplo.org)
WHERE
ahr.capture_time IS NULL AND
ahr.cancel_time IS NULL AND
csp.id IS NULL AND
(ahr.expire_time is NULL OR ahr.expire_time > NOW()) AND
acp.status IN (0,7)
]]>
SELECT ahr.* FROM action.hold_request ahr JOIN (SELECT current_copy, MAX(capture_time) AS capture_time FROM action.hold_request WHERE capture_time IS NOT NULL AND current_copy IS NOT NULL AND fulfillment_time IS NULL GROUP BY current_copy)x USING (current_copy, capture_time)
SELECT DISTINCT l.*
FROM action.unfulfilled_hold_loops l
JOIN action.unfulfilled_hold_max_loop m USING (hold)
WHERE l.count = m.max
SELECT
cbrebi.id AS id, -- so we can have a pkey in our view
uvs.id AS session,
uvs.owning_lib,
cbrebi.target_biblio_record_entry
FROM url_verify.session uvs
JOIN container.biblio_record_entry_bucket cbreb
ON (uvs.container = cbreb.id)
JOIN container.biblio_record_entry_bucket_item cbrebi
ON (cbrebi.bucket = cbreb.id)
SELECT b.id,
MAX(dcp.edit_date) AS last_delete_date
FROM biblio.record_entry b
JOIN asset.call_number cn ON (cn.record = b.id)
JOIN asset.copy dcp ON (cn.id = dcp.call_number)
WHERE NOT b.deleted
GROUP BY b.id
HAVING SUM( CASE WHEN NOT dcp.deleted THEN 1 ELSE 0 END) = 0
-- -- If we uncomment the RIGHT JOIN against biblio.record_entry, then we'll get a row for every non-deleted bib, whether it has active holds or not.
-- -- If we expect to use pcrud to query against specific bibs, we probably want to do this. However, if we're using this to populate a report, we
-- -- may not.
-- SELECT
-- bre.id AS bib_id,
-- COALESCE( z.copy_count, 0 ) AS copy_count,
-- COALESCE( z.hold_count, 0 ) AS hold_count,
-- COALESCE( z.copy_hold_ratio, 0 ) AS hold_copy_ratio
-- FROM (
SELECT
y.bre AS id,
COALESCE( x.copy_count, 0 ) AS copy_count,
y.hold_count AS hold_count,
(y.hold_count::REAL / (CASE WHEN x.copy_count = 0 OR x.copy_count IS NULL THEN 0.1 ELSE x.copy_count::REAL END)) AS hold_copy_ratio
FROM (
SELECT
(SELECT bib_record FROM reporter.hold_request_record r WHERE r.id = h.id LIMIT 1) AS bre,
COUNT(*) AS hold_count
FROM action.hold_request h
WHERE
cancel_time IS NULL
AND fulfillment_time IS NULL
-- AND NOT frozen -- a frozen hold is still a desired hold, eh?
GROUP BY 1
)y LEFT JOIN (
SELECT
(SELECT id
FROM biblio.record_entry
WHERE id = (SELECT record FROM asset.call_number WHERE id = call_number and deleted is false)
) AS bre,
COUNT(*) AS copy_count
FROM asset.copy
JOIN asset.copy_location loc ON (copy.location = loc.id AND loc.holdable)
WHERE copy.holdable
AND NOT copy.deleted
AND copy.status IN ( SELECT id FROM config.copy_status WHERE holdable )
GROUP BY 1
)x ON x.bre = y.bre
-- )z RIGHT JOIN (
-- SELECT id
-- FROM biblio.record_entry
-- WHERE NOT deleted
-- )bre ON (z.bib_id = bre.id)
SELECT *,
CASE WHEN copy_count_at_pickup_library = 0 THEN 'Infinity'::FLOAT ELSE holds_at_pickup_library::FLOAT/copy_count_at_pickup_library END AS pickup_library_ratio,
CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE holds_everywhere::FLOAT/copy_count_everywhere END AS everywhere_ratio
FROM
(SELECT bib_record as id, pickup_lib, count(DISTINCT ahr.id) AS holds_at_pickup_library, COALESCE(count(DISTINCT ac.id),0) as copy_count_at_pickup_library
FROM
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
LEFT JOIN asset.copy ac ON (ahcm.target_copy = ac.id AND ahr.pickup_lib = ac.circ_lib)
WHERE
ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
GROUP BY bib_record, pickup_lib
)x
JOIN
(SELECT bib_record as id, count(DISTINCT ahr.id) AS holds_everywhere, COALESCE(count(DISTINCT target_copy),0) as copy_count_everywhere
FROM
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
WHERE
ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
GROUP BY bib_record
)y
USING (id)
WITH counts_at_ou AS (
SELECT rhrr.bib_record AS id,
aou.id AS pickup_lib_or_desc,
COUNT(DISTINCT ahr.id) AS holds_at_or_below,
COALESCE(COUNT(DISTINCT ac.id),0) AS copy_count_at_or_below
FROM actor.org_unit aou
JOIN actor.org_unit_type aout ON (aou.ou_type = aout.id),
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
LEFT JOIN asset.copy ac ON (ahcm.target_copy = ac.id)
WHERE ahr.cancel_time IS NULL AND ahr.fulfillment_time IS NULL
AND ac.circ_lib IN (SELECT id FROM actor.org_unit_descendants(aou.id))
AND (actor.org_unit_ancestor_at_depth(ahr.pickup_lib,aout.depth)).id = (actor.org_unit_ancestor_at_depth(ac.circ_lib,aout.depth)).id
GROUP BY 1, 2
)
SELECT x.id, x.pickup_lib_or_desc, x.holds_at_or_below, x.copy_count_at_or_below,
y.holds_everywhere, y.copy_count_everywhere,
CASE WHEN copy_count_at_or_below = 0 THEN 'Infinity'::FLOAT ELSE x.holds_at_or_below::FLOAT/x.copy_count_at_or_below END AS hold_copy_ratio_at_or_below_ou,
CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE y.holds_everywhere::FLOAT/y.copy_count_everywhere END AS everywhere_ratio
FROM counts_at_ou x
JOIN (SELECT bib_record AS id, count(DISTINCT ahr.id) AS holds_everywhere, COALESCE(count(DISTINCT target_copy),0) as copy_count_everywhere
FROM
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
WHERE
ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
GROUP BY bib_record
)y
USING (id)
SELECT
ac.id,
COALESCE(MAX(actac.xact_start), ac.create_date) AS last_circ_or_create,
MAX(actac.xact_start) AS last_circ
FROM asset.copy ac
LEFT JOIN action.all_circulation actac ON ac.id = actac.target_copy
GROUP BY ac.id
-- Alternate version, say if you have migrated last checkout information in extend_reporter.legacy_circ_timestamp:
--SELECT
-- ac.id,
-- GREATEST(MAX(actac.xact_start), erlct.last_cko_ts, ac.create_date) AS last_circ_or_create,
-- GREATEST(MAX(actac.xact_start), erlct.last_cko_ts) AS last_circ
--FROM asset.copy ac
-- LEFT JOIN action.all_circulation actac ON ac.id = actac.target_copy
-- LEFT JOIN extend_reporter.legacy_circ_timestamp erlct ON ac.id = erlct.id
--GROUP BY ac.id, ac.create_date, erlct.last_cko_ts
SELECT
po.ordering_agency AS ordering_agency,
po.id AS purchase_order,
li.id AS lineitem,
lid.id AS lineitem_detail,
cpa.id AS claim_policy_action
FROM
acq.lineitem_detail lid
JOIN acq.lineitem li ON (li.id = lid.lineitem)
JOIN acq.purchase_order po ON (po.id = li.purchase_order)
JOIN acq.claim_policy cp ON (li.claim_policy = cp.id)
JOIN acq.claim_policy_action cpa ON (
cpa.claim_policy = cp.id
-- we only care about claim policy actions whose claim
-- interval we'd reached or exceeded
AND (NOW() - cpa.action_interval) > po.order_date
-- filter out all claim policy actions where claim events
-- have occurred on or after the action's action_interval
AND NOT EXISTS (
SELECT 1
FROM
acq.claim_event evt
JOIN acq.claim claim ON (
claim.id = evt.claim
AND claim.lineitem_detail = lid.id
)
WHERE
evt.event_date >= (po.order_date + cpa.action_interval)
)
)
WHERE
lid.cancel_reason IS NULL
AND li.cancel_reason IS NULL -- belt/suspenders
AND po.cancel_reason IS NULL -- belt/suspenders
AND lid.recv_time IS NULL
AND po.state = 'on-order'
ORDER BY 1, 2, 3, 4, 5
SELECT * FROM acq.lineitem_summary
WHERE item_count > (invoice_count + cancel_count)
SELECT t.*
FROM action.transit_copy t
JOIN actor.org_unit AS s ON (t.source = s.id)
JOIN actor.org_unit AS d ON (t.dest = d.id)
WHERE s.parent_ou <> d.parent_ou
SELECT copy, SUM(count) AS count, year, is_renewal FROM (
SELECT
cp.id as copy,
COUNT(circ.id),
EXTRACT(YEAR FROM circ.xact_start) AS year,
(phone_renewal OR desk_renewal OR opac_renewal) as is_renewal
FROM
asset.copy cp
JOIN action.circulation circ ON (cp.id = circ.target_copy)
GROUP BY 1, 3, 4
UNION ALL
SELECT
cp.id as copy,
COUNT(circ.id),
EXTRACT(YEAR FROM circ.xact_start) AS year,
(phone_renewal OR desk_renewal OR opac_renewal) as is_renewal
FROM
asset.copy cp
JOIN action.aged_circulation circ ON (cp.id = circ.target_copy)
GROUP BY 1, 3, 4
UNION ALL
SELECT
id as copy,
circ_count,
-1 AS year,
false as is_renewal
FROM
extend_reporter.legacy_circ_count
)x GROUP BY 1, 3, 4
SELECT ahcm.target_copy AS id,count(*) AS count
FROM
action.hold_request ahr,
action.hold_copy_map ahcm
WHERE
ahr.cancel_time IS NULL AND
ahr.fulfillment_time IS NULL AND
ahr.capture_time IS NULL AND
ahr.id = ahcm.hold
GROUP BY ahcm.target_copy