Page Comparison

SELECT
    conserv_id,
    'conserv'||convert(varchar(5), conserv_id) shortIdentifier,
    comp_key,
    substring(comp_key, 1, 5) consagent_name_id,
    an.name agentname,
    rtrim(conserv_cat) conserv_code,
    case when an.name = 'California Native Plant Society' then 'CNPS'
     when an.name = 'not applicable' then ''
     when an.name = 'International Union for Conservation of Nature and Natural Resources - 1997' then 'IUCN-1997'
     when--   when an.name = 'Convention on International Trade in Endangered Species' then 'CNPS'
         when an.name = 'Convention on International Trade in Endangered Species' then 'CITES'
     when an.name = 'Rhododendron Species Foundation' then 'RSF'
    --   when an.name = 'International Union for Conservation of Nature and Natural Resources - 2006' then 'CNPS'
     when an.name = 'International Union for Conservation of Nature and Natural Resources - 2006' then 'IUCN-2006'
     when an.name = 'South African National Biodiversity Institute' then 'SANBI'
     when an.name = 'UNEP World Conservation Monitoring Center - CITES' then 'UNEP-CITES'
     when an.name = 'New York Botanical Garden' then 'NYBG'
     when an.name = 'US Fish & Wildlife Service' then 'USFWS'
     when an.name = 'California Department of Fish & Game' then 'CADFG'
     when substring(comp_key, 1, 5) = '60801' or an.name = 'IUCN/SSC Global Tree Specialist Group, Red List of Rhododendrons - 2011' then 'IUCN-Rhododendrons-2011'
     when substring(comp_key, 1, 5) = '60804' or an.name = 'International Union for Conservation of Nature and Natural Resources - 2011.1' then 'IUCN-2011.1'
     when substring(comp_key, 1, 5) = '60849' or an.name = 'International Union for Conservation of Nature and Natural Resources - 2011.2' then 'IUCN-2011.2'
     when substring(comp_key, 1, 5) = '60905' or an.name = 'International Union for Conservation of Nature and Natural Resources - 2012.1' then 'IUCN-2012.1'
     when substring(comp_key, 1, 5) = '60917' or an.name = 'International Union for Conservation of Nature and Natural Resources - 2012.2' then 'IUCN-2012.2'
     when an.name = 'Non-conservation publication' then 'Non-conservation pub'
     when conserv_id in (38, 39, 41, 42, 47) then ''
     end as conservCode_append,
    cat_descr conserv_descr,
    case when red_dot = 1 then 'red dot on label'
      end as scope_note_red_dot
FROM
    dbo.conserv_code c
left outer join agent_name an on (substring(comp_key, 1, 5) = convert(varchar(20), an.agent_id) and an.name_type = 'pref')
-- where conserv_id not in (38, 39, 41, 42, 47)
order by comp_key