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 DISTINCT l.*
FROM action.unfulfilled_hold_loops l
JOIN action.unfulfilled_hold_max_loop m USING (hold)
WHERE l.count = m.max
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)
) 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
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
li.id AS lineitem,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE lineitem = li.id
) AS item_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE recv_time IS NOT NULL AND lineitem = li.id
) AS recv_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE cancel_reason IS NOT NULL AND lineitem = li.id
) AS cancel_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE NOT debit.encumbrance AND lineitem = li.id
) AS invoice_count,
(
SELECT COUNT(DISTINCT(lid.id))
FROM acq.lineitem_detail lid
JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
WHERE lineitem = li.id
) AS claim_count,
(
SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
WHERE lid.cancel_reason IS NULL AND lineitem = li.id
) AS estimated_amount,
(
SELECT SUM(debit.amount)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE debit.encumbrance AND lineitem = li.id
) AS encumbrance_amount,
(
SELECT SUM(debit.amount)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE NOT debit.encumbrance AND lineitem = li.id
) AS paid_amount
FROM acq.lineitem AS li
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
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
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