Dispersal SQL Report

-- dispersal report SQL version
-- working on a variation off weeding

-- what's different from a weeding in part is that the items aren't actively circulating here anymore
-- so we want things that haven't circulated in the last 6 months rather than three years

-- instead of all we also want a set number of them and to order by how recently they had activity
-- so as to get slightly more recent items

-- very simple, superceded by the dispersal postgres functions
SELECT * FROM (
        SELECT COUNT(acirc.id), ac.id AS copy_id, ac.barcode AS "barcode", acl.NAME AS "shelving location",
        acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON ac.LOCATION = acl.id
        LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
        JOIN asset.call_number acn ON acn.id = ac.call_number
        JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
        JOIN actor.org_unit child ON child.id = ac.circ_lib
        JOIN actor.org_unit parent ON parent.id = child.parent_ou
        WHERE acl.NAME ILIKE 'adult large print'
        AND acirc.xact_start IS NULL
        AND date(ac.create_date) <= NOW() - INTERVAL '6 months'
        AND child.shortname = 'YCL-RH'
        AND ac.deleted = FALSE
        GROUP BY 2, 3, 4, 5, 6
        UNION ALL
        SELECT COUNT(acirc.id), ac.id AS copy_id, ac.barcode AS "barcode", acl.NAME AS "shelving location",
        acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON ac.LOCATION = acl.id
        LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
        JOIN asset.call_number acn ON acn.id = ac.call_number
        JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
        JOIN actor.org_unit child ON child.id = ac.circ_lib
        JOIN actor.org_unit parent ON parent.id = child.parent_ou
        WHERE acl.NAME ILIKE 'adult large print'
        AND date(ac.create_date) <= NOW() - INTERVAL '6 months'
        AND child.shortname = 'YCL-RH'
        AND ac.deleted = FALSE
        GROUP BY 2, 3, 4, 5, 6
        HAVING MAX(acirc.xact_start) < NOW() - INTERVAL '6 months'
        ORDER BY 4
) disperse ORDER BY "last circulation" DESC LIMIT 200
;


-- rewritten to account for different circ libraries within a single shelving location
-- not needed long term 2015/02/04

SELECT * FROM (
        SELECT COUNT(acirc.id), ac.id AS copy_id, ac.barcode AS "barcode", acl.NAME AS "shelving location",
        acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON ac.LOCATION = acl.id
        LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
        JOIN asset.call_number acn ON acn.id = ac.call_number
        JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
        JOIN actor.org_unit child ON child.id = ac.circ_lib
        JOIN actor.org_unit parent ON parent.id = child.parent_ou
        WHERE acl.NAME ILIKE 'adult large print'
        AND acirc.xact_start IS NULL
        AND date(ac.create_date) <= NOW() - INTERVAL '6 months'
        AND child.shortname = 'YCL-RH'
        AND ac.deleted = FALSE
        AND ac.circ_lib = 127
        GROUP BY 2, 3, 4, 5, 6
        UNION ALL
        SELECT COUNT(acirc.id), ac.id AS copy_id, ac.barcode AS "barcode", acl.NAME AS "shelving location",
        acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON ac.LOCATION = acl.id
        LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
        JOIN asset.call_number acn ON acn.id = ac.call_number
        JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
        JOIN actor.org_unit child ON child.id = ac.circ_lib
        JOIN actor.org_unit parent ON parent.id = child.parent_ou
        WHERE acl.NAME ILIKE 'adult large print'
        AND date(ac.create_date) <= NOW() - INTERVAL '6 months'
        AND child.shortname = 'YCL-RH'
        AND ac.deleted = FALSE
        AND ac.circ_lib = 127
        GROUP BY 2, 3, 4, 5, 6
        HAVING MAX(acirc.xact_start) < NOW() - INTERVAL '6 months'
        ORDER BY 4
) disperse ORDER BY "last circulation" DESC LIMIT 50
;