tadl.ipl_copy_add_remove.sql

CREATE OR REPLACE VIEW tadl.ipl_copy_add_remove 
(
	add_date,
	remove_date,
	id,
	is_donated,
	price,
	cost
) 
AS
WITH tadl_copy_cost AS (
	SELECT DISTINCT ON (owning_copy)
	owning_copy,
	tadl.attempt_cast(value,'numeric(8,2)')::numeric(8,2) AS cost
	FROM asset.copy_note acn
	WHERE UPPER(acn.title) = 'COST'
	ORDER BY owning_copy, create_date DESC

), tadl_is_donated AS (
	SELECT owning_copy as copy, TRUE AS is_donated
	FROM asset.stat_cat_entry_copy_map
	WHERE stat_cat = 24 -- How acquired
	AND stat_cat_entry = 517 -- Donated
)
SELECT
	COALESCE(acp.active_date, acp.create_date) AS add_date,
	CASE WHEN acp.deleted THEN acp.edit_date
		 WHEN acp.status = 13 THEN acp.edit_date
		 ELSE NULL
		 END AS remove_date,
	acp.id,
	COALESCE(tadl_is_donated.is_donated, FALSE) as is_donated,
	price,
	COALESCE(acp.cost,tcc.cost,CASE WHEN is_donated THEN price*0.20 ELSE price*0.80 END)::numeric(8,2) AS cost
FROM asset.copy acp
LEFT JOIN asset.call_number acn ON acn.id = acp.call_number
LEFT JOIN tadl_copy_cost tcc ON (acp.id = tcc.owning_copy)
LEFT JOIN tadl_is_donated ON (acp.id = tadl_is_donated.copy)
WHERE circ_lib = 24 -- TADL-IPL
AND acp.status <> 9 -- exclude "On order"
AND acp.call_number <> -1 -- exclude pre-cats
AND acp.barcode NOT LIKE 'ILL%' -- exclude legacy ILL copies
-- newer ILLs identified by call number label or circ_modifier (usually both)
AND acn.label NOT LIKE 'ILL%'
AND (acp.circ_modifier is null OR acp.circ_modifier NOT IN ('ILL','ILL-RET'));

select * from tadl.ipl_copy_add_remove ORDER BY add_date DESC NULLS LAST;