Handy SQL

Shelving Location Report

select http://sl.id  sl_id, sl.code sl_code, sl.name sl_name, sl.agency_id sl_agency, lms.id lms_id, lms.code lms_code, lms.name lms_name, l.id parent_loc_id, l.agency_fk parent_loc_agency, l.code parent_loc_code, l.name parent_loc_name, l.type parent_loc_type
from shelving_location sl,  
     host_lms lms,
	 location l
where sl.host_system_id = lms.id
and sl.location_id = l.id

Location Report

select l.*, lms.*
from
	 location l,
	 host_lms lms
where l.host_system_id = lms.id
and l.type='BRANCH'

Needed reports

Locations without agency set

Shelving Location Without Locations

Set all Tulsa locations to have Tulsa agency

update location set agency_fk = '102f96bc-11c5-5262-9ce7-173aa03c58f3'
where host_system_id = '1f21830c-a893-5dac-af6e-d7f9bddcb2be'

Set all SGCL locations to SGCL agency

update location set agency_fk = 'cb297647-c6f9-591c-9e8b-c58dfea986a9'
where host_system_id = 'eb745468-c69c-5f7e-9bd4-cf3cf8db6cd9'

Checking polling schedule

select pr.id, pr.date_created, pr.active_workflow, pr.patron_hostlms_code, pr.status_code, pr.local_request_status, pr.local_item_id, pr.local_item_status, pr.next_scheduled_poll, sr.local_item_status, sr.local_status, sr.local_agency from patron_request pr, supplier_request sr where sr.patron_request_id = pr.id order by date_created;