SQL Workshop Evergreen Conference 2015

/*
1 Cup of Coffee

These reports are straightforward without difficult transforms or complicated joins but may require
you to be a little more awake to notice the (in hindsight sometimes) obvious.

"untargeted" holds -- i.e. records/items with holds on them where all copies have gone
lost/missing/claimed returned/etc. Ideally run regularly so we can promptly follow up with ILL or replacement.

Reports making use of local stat cats & their variables

Extract circulation stats for a specific month/year
*/

select count(acirc.id) as Circulation, extract(year from acirc.xact_start) as Year,
acl.name as "Copy Location"
from action.circulation acirc
join asset.copy ac on ac.id = acirc.target_copy
join asset.copy_location acl on acl.id = ac.location
where ac.circ_lib = 127
group by 2, 3
;

select count(aih.id), ssr.author, ssr.title
from action.in_house_use aih
join asset.copy ac on ac.id = aih.item
join asset.call_number acn on acn.id = ac.call_number
join reporter.super_simple_record ssr on ssr.id = acn.record
where date(aih.use_time) >= '2015-01-01'
group by 2, 3
;

select ac.*
from asset.copy ac
left join action.circulation acirc on acirc.target_copy = ac.id
where date(ac.create_date) >= now() - interval '1 year'
and date(ac.create_date) <= now() - interval '6 months'
and acirc.id is null
limit 100
;










select * from action.hold_request limit 10;

select ac.barcode as "Item's Barcode"
from action.hold_request ahr
join asset.copy ac on ac.id = ahr.target
where ahr.fulfillment_time is null and ahr.capture_time is null
and ahr.hold_type = 'C' and ac.status in (101,14,13,16,4)
and ahr.cancel_time is null
limit 10
;

UNION ALL
select ac.barcode
from action.hold_request ahr
join biblio.record_entry bre on bre.id = ahr.target
join asset.call_number acn on acn.record = bre.id
join asset.copy ac on ac.call_number = acn.id
where ahr.fulfillment_time is null and ahr.capture_time is null
and ahr.hold_type = 'T' and ac.status in (101,14,13,16,4)
and ahr.cancel_time is null
;



select * from config.copy_status;



/*
Will Need Edits

Now we get into the reports that require a bit more planning and we may find ourselves going back and tweaking a fair bit.

Purchase alert report *
Compare number of active holds on a title to number of holdable items on same title**
Identify active holds that have remained unfilled despite newer holds on the same title being filled
Weeding report *

* These two reports are one that I have well traveled versions of.  We will probably do these and I'll start with
the planning and though process but part way skip and show my completed versions and how they work.
** This is really a subset of the purchase alert but I'll show how it can be looked at a little differently if
the crowd is interested.
*/



SELECT bd.bib_id, parent.shortname AS "org unit", bd.title, bd.author, bd.isbn, SUM(bd.copies) AS copies, SUM(bd.holds_count) AS "holds count",
ROUND(SUM(bd.holds_count)/SUM(bd.copies)) AS "holds count per copy"
 FROM (
        SELECT all_holds.bib_id, aou.id AS org, rmsr.title, rmsr.author, rmsr.isbn,
        COUNT(DISTINCT ac.id) AS "copies", COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) AS "holds_count"
        FROM ((
        SELECT hold_request.target, hold_request.pickup_lib
        FROM ACTION.hold_request
        WHERE hold_request.hold_type = 'T'::TEXT
        AND hold_request.fulfillment_time IS NULL
        AND hold_request.cancel_time IS NULL
        UNION ALL
        SELECT bre.id, ahr.pickup_lib
        FROM ACTION.hold_request ahr
        JOIN asset.call_number acn ON ahr.target = acn.id
        JOIN biblio.record_entry bre ON acn.record = bre.id
        WHERE ahr.hold_type = 'V'::TEXT
        AND ahr.fulfillment_time IS NULL
        AND ahr.cancel_time IS NULL)
        UNION ALL
        SELECT bre.id, ahr.pickup_lib
        FROM ACTION.hold_request ahr
        JOIN asset.COPY ac ON ahr.target = ac.id
        JOIN asset.call_number acn ON ac.call_number = acn.id
        JOIN biblio.record_entry bre ON acn.record = bre.id
        WHERE ahr.hold_type = 'C'::TEXT
        AND ahr.fulfillment_time IS NULL
        AND ahr.cancel_time IS NULL)
        all_holds(bib_id, pickup_lib)
        JOIN reporter.materialized_simple_record rmsr ON rmsr.id = all_holds.bib_id
        JOIN actor.org_unit aou ON aou.id = all_holds.pickup_lib
        JOIN asset.call_number acn ON acn.record = all_holds.bib_id
        JOIN asset.COPY ac ON ac.call_number = acn.id
        WHERE ac.circ_lib = all_holds.pickup_lib
        GROUP BY 1, 2, 3, 4, 5
        HAVING COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) > 3
) bd
JOIN actor.org_unit child ON child.id = bd.org
JOIN actor.org_unit parent ON parent.id = child.parent_ou
GROUP BY 1, 2, 3, 4, 5
ORDER BY 2 ASC, 5 DESC
;

select *
from metabib.real_full_rec mrf
where mrf.tag = '856' and mrf.value ilike '%loc.gov%'
limit 50
;
SELECT SUM(a.copy_id) AS "Videos", a.org2_shortname
FROM (
        SELECT COUNT(COPY.id) AS copy_id, org2.shortname AS org2_shortname
        FROM asset.COPY COPY
        JOIN actor.org_unit org ON org.id = COPY.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.call_number acn ON acn.id = COPY.call_number
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record
        WHERE COPY.deleted = FALSE AND date(COPY.create_date) <= '2013-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
UNION ALL
        SELECT COUNT(COPY.id) AS copy_id, org2.shortname AS org2_shortname
        FROM asset.COPY COPY
        JOIN actor.org_unit org ON org.id = COPY.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        JOIN asset.call_number acn ON acn.id = COPY.call_number
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record
        WHERE COPY.deleted = TRUE AND date(COPY.create_date) <= '2013-06-30'
        AND date(COPY.edit_date) >= '2012-07-01' AND date(COPY.edit_date) <= '2013-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
) a
GROUP BY 2
ORDER BY 2
;






/*
Pretend they didn't ask

billing reports
*/