View difference between Paste ID: 2icc5Ym3 and CWs48nZk

 /*
 Section G: LIBRARY USERS, VISITS, COMPUTER AND INTERNET
 
                         Registered Users
             G1 Adult__________________________
             G2 Juvenile__________________________
             G3 TOTAL (G1 + G2)__________________________
 
 counts patron accounts under the age of 18 by the end of the fiscal year where the age is blank due to the dob
 being null because we used to allow that.  we look to see if the account is labeled as juvenile in the description,
 this will change after the Fall 2015 profile consolidaiton and we should be able to do it more cleanly off
 pgt.name
 
 I check for dob first becuase it could be an adult in a child account but I assume if the dob is null that it is on the
 exception list above and a child, I look to see if it's marked deleted and if it was marked past the cut off
 and if it was created in the last three years
 
 */
 
 select a.library as "Library", a.adults as "Adults - G1", j.juveniles as "Juveniles - G2", a.adults+j.juveniles as "Total - G3"
 from (
 	SELECT org2.shortname as "library", COUNT(usr.id) AS "adults"
 	FROM actor.usr usr
 	JOIN actor.org_unit org ON org.id = usr.home_ou
 	JOIN actor.org_unit org2 ON org2.id = org.parent_ou
 	join permission.grp_tree pgt on pgt.id = usr.profile
 	WHERE
 	(
         	date(usr.create_date) >= '2012-07-01'
         	OR
         	(
                 	usr.deleted = FALSE
             	    AND
         	        date(usr.expire_date) >= '2012-07-01'
     	    )
 	)
 	AND
 	        (
                 	date(usr.dob) <= date('1997-07-01')
             	    OR
         	        (dob IS NULL AND pgt.description not ilike '%juv%')
     	    )
 	GROUP BY 1
 	ORDER BY 1
 ) a
 join (
 	SELECT org2.shortname as "library", COUNT(usr.id) AS "juveniles"
 	FROM actor.usr usr
 	JOIN actor.org_unit org ON org.id = usr.home_ou
 	JOIN actor.org_unit org2 ON org2.id = org.parent_ou
 	join permission.grp_tree pgt on pgt.id = usr.profile
 	WHERE
 	(
     	    date(usr.create_date) >= '2012-07-01'
         	OR
         	(
             	    usr.deleted = FALSE
                		AND
                 	date(usr.expire_date) >= '2012-07-01'
         	)
 	)
 	AND
         	(
 	                date(usr.dob) >= date('1997-07-01')
     	            OR
         	        -- next year after next round we should be able to do this more cleanly off pgt.name
             	    (dob IS NULL AND pgt.description ilike '%juv%')
         	)
 	GROUP BY 1
 	ORDER BY 1
 ) j on j.library = a.library;
 
 
 
 /*
 
 
 Section H: REFERENCE, CIRCULATION
 
             Circulation Transactions and Interlibrary Loan H2 - H11 (In all sections, if no circulation to report, enter 0)
             Juvenile Circulation (may include Teen/YA circulation per library policy)
             H2 Print__________________________
             H3 Non-Print (not books or electronic/downloadable items)__________________________
             H4 TOTAL JUVENILE CIRCULATION (H2 + H3)__________________________
 
             Adult Circulation (may include Teen/YA circulation per library policy)
             H5 Print__________________________
             H6 Non-Print (not books or electronic/downloadable items)__________________________
             H7 TOTAL (H5 + H6)__________________________
 
 */
 
--------------------------------------- circ by material format from circ mod and shelving location
+-------------------------------------- circ by material format from leader and audience
 -------------------------------------- looks for a = language material = book
 -------------------------------------- i = audio books, j = music, g = projected medium aka 'not a' = non-print
 -------------------------------------- non-cat circs are all added to print materials
 ------------------------------------------------------------------------------------------------------------------
 ------------------------------------------------------------------------------------------------------------------
-/* create a table and map per circulation */
+
 drop table if exists rogan.action_circulation;
 drop table if exists rogan.circ_ldrs;
-select * from action.circulation where date(create_time) between '2014-07-01' and '2015-06-30';
+
 create table rogan.action_circulation as
-alter table rogan.action_circulation add column print boolean default TRUE;
+select * from action.circulation circ
 WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30';
-/* map print = true based on these circ mods */
+
 create table rogan.circ_ldrs as
-update rogan.action_circulation set print = false where target_copy in (
+SELECT circ.id as "circ_id", SUBSTRING(meta.VALUE FROM 7 FOR 1) as "circ_ldr"
-	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
+FROM rogan.action_circulation circ
-	where ac.circ_modifier in ('ELECTRONIC EQUIPMENT','VIDEO','AUDIO','EREADER','PLAYAWAY','AUDIO BOOK',
+JOIN asset.copy copy ON copy.id = circ.target_copy
-	'THEME KIT','EQUIPMENT','KIT','INTERNET','GAME','LEASE DVD')
+JOIN asset.call_number call ON call.id = copy.call_number
-);
+LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
 AND meta.tag = 'LDR'
-/* then it got a lot more manual looking at shelving locations.  I made a review of a sample of five thousand shelving
+
-location entries and based on that assumed that copies with shelving locations saying AUDIO or VIDEO or EQUIPMENT
+
-should be changed to non-print then I re-ran it with exclusions and found more like DVD */
+
 juv_a.circs as "Juvenile Print Circs - H2", juv_not_a.circs as "Juvenile Non-Print Circs - H3",
-update rogan.action_circulation set print = false where target_copy in (
+juv_a.circs+juv_not_a.circs+coalesce(non.circs,0) as "Total Juvenile Circs - H4",
-	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
+adult_a.circs as "Adult Print Circs - H5", adult_not_a.circs as "Adult Non-Print Circs - H6",
-	join asset.copy_location acl on acl.id = ac.location
+
-	where ac.circ_modifier in ('ELECTRONIC EQUIPMENT','VIDEO','AUDIO','EREADER','PLAYAWAY','AUDIO BOOK',
+
-	'THEME KIT','EQUIPMENT','KIT','INTERNET','GAME','LEASE DVD')
+
-	and (acl.name ilike '%audio%' or acl.name ilike '%video%' or acl.name ilike '%equipment%' or acl.name ilike '%dvd%'
+
-	or acl.name ilike '%books on cd%' or acl.name ilike '%books on cassette%' or acl.name ilike '%music cd%' or
+
-	acl.name ilike '%adult 360%' or acl.name ilike '%adult ps2%')
+
-);
+        JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
-/* now I will go through all distinct occurances of the library systems and these circ mods with other shelving
+        join asset.copy_location acl on acl.id = copy.location
-location names excluding those that had audio, video or unknown in the name */
+        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
         WHERE ldrs.circ_ldr = 'a'
-select DISTINCT aou.parent_ou, ac.circ_modifier, acl.name
+        and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
-from asset.copy ac
+
-join actor.org_unit aou on aou.id = ac.circ_lib
+
-join asset.copy_location acl on acl.id = ac.location
+
-where ac.circ_modifier in ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
+
-and acl.name not ilike '%audio%' and acl.name not ilike '%audio%' and acl.name not ilike '%video%'
+
-and acl.name not ilike '%equipment%' and acl.name not ilike '%dvd%' and acl.name not ilike '%books on cd%'
+
-and acl.name not ilike '%books on cassette%' and acl.name not ilike '%music cd%' and
+
-acl.name not ilike '%adult 360%' and acl.name not ilike '%adult ps2%'
+        JOIN asset.copy copy ON copy.id = circ.target_copy
-order by 1, 2;
+        JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
-alter table rogan.action_circulation add column adult boolean default true;
+        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
         WHERE ldrs.circ_ldr = 'a'
-update rogan.action_circulation set adult = false where target_copy in (
+        and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
-	select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
+
-	join asset.copy_location acl on acl.id = ac.location
+
-	where acl.name ilike '%juv%' or acl.name ilike '%young%'
+
-);
+
         FROM rogan.action_circulation circ
         JOIN actor.org_unit org ON org.id = circ.circ_lib
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
-juv_a.circs+juv_not_a.circs as "Total Juvenile Circs - H4",
+        JOIN asset.copy copy ON copy.id = circ.target_copy
-adult_a.circs+coalesce(non.circs,0) as "Adult Print Circs - H5", adult_not_a.circs as "Adult Non-Print Circs - H6",
+        JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
         LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
         WHERE ldrs.circ_ldr != 'a'
         and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
         GROUP BY 2
 ) juv_not_a on juv_not_a.shortname = juv_a.shortname
-        WHERE circ.print = true and circ.adult = false
+
         SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
         FROM rogan.action_circulation circ
         JOIN actor.org_unit org ON org.id = circ.circ_lib
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
         JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
-        WHERE circ.print = true and circ.adult = true
+        LEFT OUTER JOIN rogan.circ_ldrs ldrs ON ldrs.circ_id = circ.id
         WHERE ldrs.circ_ldr != 'a'
         and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
         GROUP BY 2
 ) adult_not_a on adult_not_a.shortname = juv_a.shortname
 left join (  SELECT coalesce(count(circ.id),0) AS circs, org2.shortname AS shortname
         FROM action.non_cataloged_circulation circ
         left JOIN actor.org_unit org ON org.id = circ.circ_lib
-        WHERE circ.print = false and circ.adult = false
+
         where date(circ.circ_time) >= '2014-07-01' AND date(circ.circ_time) <= '2015-06-30'
         GROUP BY 2
 ) non on non.shortname = juv_a.shortname
 ;
 
 drop table if exists rogan.action_circulation;
 drop table if exists rogan.circ_ldrs;
-        WHERE circ.print = false and circ.adult = true
+
 /*
 
 -- original form before I created the other tables to query from so it wouldn't take so long to run
 
 SELECT juv_a.shortname as "Library",
 juv_a.circs as "Juvenile Print Circs - H2", juv_not_a.circs as "Juvenile Non-Print Circs - H3",
 juv_a.circs+juv_not_a.circs as "Total Juvenile Circs - H4",
 adult_a.circs as "Adult Print Circs - H5", adult_not_a.circs as "Juvenile Non-Print Circs - H6",
 adult_a.circs+adult_not_a.circs as "Total Adult Circs - H7"
 FROM (
         SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
         FROM action.circulation circ
         JOIN actor.org_unit org ON org.id = circ.circ_lib
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
         JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
         LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
         WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
         AND meta.tag = 'LDR'
         AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
         and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
         GROUP BY 2
 ) juv_a
 join (
         SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
         FROM action.circulation circ
         JOIN actor.org_unit org ON org.id = circ.circ_lib
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
         JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
         LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
         WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
         AND meta.tag = 'LDR'
         AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
         and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
         GROUP BY 2
 ) adult_a on adult_a.shortname = juv_a.shortname
 join (
         SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
         FROM action.circulation circ
-	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
+
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
         JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
         LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
         WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
         AND meta.tag = 'LDR'
         AND SUBSTRING(meta.VALUE FROM 7 FOR 1) != 'a'
         and ( acl.name ilike '%juv%' or acl.name ilike '%young%' )
         GROUP BY 2
-	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
+
 join (
         SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
         FROM action.circulation circ
         JOIN actor.org_unit org ON org.id = circ.circ_lib
         JOIN actor.org_unit org2 ON org2.id = org.parent_ou
         JOIN asset.copy copy ON copy.id = circ.target_copy
         JOIN asset.call_number call ON call.id = copy.call_number
         join asset.copy_location acl on acl.id = copy.location
         LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
         WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30'
         AND meta.tag = 'LDR'
         AND SUBSTRING(meta.VALUE FROM 7 FOR 1) != 'a'
         and ( acl.name not ilike '%juv%' or acl.name not ilike '%young%' )
         GROUP BY 2
 ) adult_not_a on adult_not_a.shortname = juv_a.shortname
 ;
 */
 
 
 /*
 
             Interlibrary Loans
             H14   Provided to another library__________________________
             H15   Received from another library__________________________
 
 */
 
 /*
 -- borrowers at other libraries, these are like reciprocal borrowers
 select parent.shortname as "Borrowing Library", count(circ.id) as "circs" from action.circulation circ
 join actor.usr au on au.id = circ.usr
 join actor.org_unit aou on aou.id = au.home_ou
 join actor.org_unit parent on parent.id = aou.parent_ou
 join actor.org_unit aou2 on aou2.id = circ.circ_lib
 join actor.org_unit parent2 on parent2.id = aou2.parent_ou
 WHERE date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-30' and parent.id != parent2.id
 group by 1 order by 1
 ;
 */
 
 select loaners.shortname, loaners.loans as "Provided to another library - H14", borrowers.borrowers as "Received from another library - H15"
 from ( SELECT copyparent.shortname, COUNT(circ.id) as "loans"
 	FROM ACTION.circulation circ
 	JOIN asset.COPY ac ON ac.id = circ.target_copy
 	JOIN actor.org_unit copychild ON copychild.id = ac.circ_lib
 	JOIN actor.org_unit copyparent ON copyparent.id = copychild.parent_ou
 	JOIN actor.org_unit circchild ON circchild.id = circ.circ_lib
 	JOIN actor.org_unit circparent ON circparent.id = circchild.parent_ou
 	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-01'
 	GROUP BY 1
 ) loaners
 join (
 SELECT circparent.shortname, COUNT(circ.id) as "borrowers"
 	FROM ACTION.circulation circ
 	JOIN asset.COPY ac ON ac.id = circ.target_copy
 	JOIN actor.org_unit copychild ON copychild.id = ac.circ_lib
 	JOIN actor.org_unit copyparent ON copyparent.id = copychild.parent_ou
 	JOIN actor.org_unit circchild ON circchild.id = circ.circ_lib
 	JOIN actor.org_unit circparent ON circparent.id = circchild.parent_ou
 	WHERE copyparent.id != circparent.id AND date(circ.xact_start) >= '2014-07-01' AND date(circ.xact_start) <= '2015-06-01'
 	GROUP BY 1
 ) borrowers on borrowers.shortname = loaners.shortname
 order by 1
 ;