Annual Cleanup - Abandoned Records w/ No Holdings

-- first step, create a table of call number IDs with no holdings
CREATE TABLE rogan.acn_no_holdings AS
SELECT call.id
FROM biblio.record_entry bib
LEFT JOIN asset.call_number call ON (bib.id = call.record)
LEFT JOIN asset.COPY COPY ON (call.id = COPY.call_number)
WHERE bib.deleted IS FALSE AND call.deleted = FALSE
AND (COPY.deleted IS NULL OR NOT COPY.deleted)
AND (call.deleted IS NULL OR NOT call.deleted)
AND bib.create_date < NOW() - INTERVAL '30 days'
and bib.source not in (101,102)
GROUP BY 1
HAVING COUNT(COPY.id) = 0;

-- now bibs
CREATE TABLE rogan.bre_no_holdings AS
SELECT bib.id
FROM biblio.record_entry bib
LEFT JOIN asset.call_number call ON (bib.id = call.record)
LEFT JOIN asset.COPY COPY ON (call.id = COPY.call_number)
WHERE bib.deleted IS FALSE
AND (COPY.deleted IS NULL OR NOT COPY.deleted)
AND (call.deleted IS NULL OR NOT call.deleted)
AND bib.create_date < NOW() - INTERVAL '30 days'
and bib.source not in (101,102)
GROUP BY 1
HAVING COUNT(COPY.id) = 0;


SELECT COUNT(id) FROM rogan.acn_no_holdings;
SELECT COUNT(id) FROM rogan.bre_no_holdings;

-- see what bibs come u

UPDATE asset.call_number SET deleted = TRUE WHERE record IN
(SELECT id FROM rogan.acn_no_holdings) AND NOT deleted;

-- And now the bibs,

UPDATE biblio.record_entry SET deleted = TRUE WHERE id IN
(SELECT id FROM rogan.bre_no_holdings);