Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop table if exists rogan.gale_marketing;
- create table rogan.gale_marketing as
- select au.id, aua.street1, aua.street2, aua.city, aua.state, aua.post_code as zip
- from actor.usr au
- join actor.usr_address aua on aua.id = au.mailing_address
- join actor.org_unit child_org on child_org.id = au.home_ou
- join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
- where parent_org.shortname = 'YCL' and au.deleted = false and au.active = true
- and au.expire_date > now() - interval '2 years'
- -- and (aua.post_code ilike '297%' or aua.post_code ilike '28%')
- and aua.county = 'York' and aua.post_code != '' and street1 != ''
- group by 1, 2, 3, 4, 5, 6
- ;
- alter table rogan.gale_marketing add column event_time timestamptz;
- alter table rogan.gale_marketing add column circ_time timestamptz;
- update rogan.gale_marketing set event_time = r.ret
- from ( select rgm.id as rd, max(aua.event_time) as ret
- from rogan.gale_marketing rgm
- join actor.usr_activity aua on aua.usr = rgm.id
- group by 1) r
- where r.rd = id ;
- update rogan.gale_marketing set circ_time = r.ret
- from ( select rgm.id as rd, max(acirc.create_time) as ret
- from rogan.gale_marketing rgm
- join action.circulation acirc on acirc.usr = rgm.id
- group by 1) r
- where r.rd = id ;
- select * from rogan.gale_marketing limit 10;
- select street1 as address, street2, city, state, zip, date(greatest(circ_time,event_time)) as "date"
- from rogan.gale_marketing
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement