Location - plch/sierra-sql GitHub Wiki

Find Stat Group Info by Branch Names

select 
bn."name" as branch_name,
sgn."name" as stat_group_name,
sg.code_num, 
location_code 
from 
sierra_view.statistic_group sg 
join sierra_view.statistic_group_name sgn on sgn.statistic_group_id = sg.id 
join sierra_view."location" as l on l.code = sg.location_code 
join sierra_view.branch b on b.code_num = l.branch_code_num 
join sierra_view.branch_name as bn on bn.branch_id = b.id  
where bn."name" in ('Hyde Park', 'Walnut Hills')
order by 1

Create custom "Sub-Branch" names -- useful for the Main Library that has

SET search_path TO sierra_view;
--with location_data as (
	select
	  loc.code as location_code,
	  ln.name as location_name,
	  case
	    when loc.code in ('1') then 'Main Library - Cleanup'
	    when (
	      length(loc.code) > 1
	      and substr(loc.code, 1, 2) in ('4c', '1z', 'zz', 'yx')
	    ) then 'Main Library - Cleanup'
	    when (
	      length(loc.code) > 1
	      and substr(loc.code, 1, 2) in ('1h')
	    ) then 'Main Library - Homework Help'
	    when (
	      length(loc.code) > 1
	      and substr(loc.code, 1, 2) in ('3c', '3d', '3e', '3g')
	    ) then 'Main Library - Genealogy and Local History'
	    when (
	      length(loc.code) > 1
	      and substr(loc.code, 1, 2) in ('no') 
	      and ln.name NOT LIKE '~%' -- filter out virtual locations
	    ) then 'Main Library - Unknown'
	    when (
	      length(loc.code) > 1
	      and substr(loc.code, 1, 2) in (
	        '1l',
	        '1c',
	        '1p',
	        '1f',
	        '2e',
	        '2g',
	        '2k',
	        '2m',
	        '2n',
	        '2r',
	        '2s',
	        '2t',
	        '3a',
	        '3h',
	        '3l',
	        '3r',
	        '4d'
	      )
	    ) then 'Main Library - Popular Library'
	    else bn.name
	  end as sub_branch_name,
	  loc.is_public,
	  loc.is_requestable --,
--	  bn.name as branch_name
	from
	  "location" as loc
	  join location_name as ln on ln.location_id = loc.id
	  join branch as b on b.code_num = loc.branch_code_num
	  join branch_name as bn on bn.branch_id = b.id
--	order by 
--	  sub_branch_name
--)
--select
--	location_code,
--	location_name,
--	my_loc_name,
--	is_public,
--	is_requestable,
--	branch_name 
--from 
--	location_data
--	  order by
--	  case
--	    when branch_name = 'Main Library' then ' '
--	    else branch_name
--	  end,
--	  case
--	    when my_loc_name is not null then ' '
--	    else my_loc_name
--	  end,
--	  -- branch_name,
--	  location_code