Public Relations Reports

-- Public Relations reports organized by dates used

-- anytime -> top ten titles by circulation in date range given
-- by default it's calculating for the last year but you could easily modify it to look within
-- specific dates

select count(circ.id), call.record,
array_accum(DISTINCT(maf.value)) AS "Author",
array_accum(DISTINCT(mtf.value)) AS "Title"
from action.circulation circ
join asset.copy copy on copy.id = circ.target_copy
join asset.call_number call on call.id = copy.call_number
join metabib.author_field_entry maf ON call.record = maf.source
join metabib.title_field_entry mtf ON call.record = mtf.source
where circ.xact_start > now() - interval '1 year'
group by call.record
order by 1 desc
limit 11
;


-- How about Christmas titles in the holiday Season? These are the top 10.

select count(circ.id), call.record,
array_accum(DISTINCT(maf.value)) AS "Author",
array_accum(DISTINCT(mtf.value)) AS "Title"
from action.circulation circ
join asset.copy copy on copy.id = circ.target_copy
join asset.call_number call on call.id = copy.call_number
join metabib.author_field_entry maf ON call.record = maf.source
join metabib.title_field_entry mtf ON call.record = mtf.source
where date(circ.xact_start) > '2013-09-30' and date(circ.xact_start) < '2014-01-01'
and mtf.value ilike '%christmas%'
group by call.record
order by 1 desc
limit 11
;

-- Or maybe just a raw count

select count(circ.id)
from action.circulation circ
join asset.copy copy on copy.id = circ.target_copy
join asset.call_number call on call.id = copy.call_number
join metabib.author_field_entry maf ON call.record = maf.source
join metabib.title_field_entry mtf ON call.record = mtf.source
where date(circ.xact_start) > '2013-09-30' and date(circ.xact_start) < '2014-01-01'
and mtf.value ilike '%christmas%'
;