SELECT DISTINCT 
r.MONITORING_LOC_ID       AS "WIN ID",
UPPER(
TRIM(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(r.LOCATION_NAME
,  '*', ' '),  '#', ''),  '"', ''),  ',', ' '),  '|', ' '),  '!', ' '),  ' -- ', ' '),  ' - ', ' '),  '-', ' '),  '''', ''),  '.', '')
,  '+', ''),  '@', 'At'),  '&', 'And'),  '<', ' '),  '?', ' '),  '/', ' '),  '\', ' '),  ':', ' '),  '>', ' '),  '[', ' '),  ']', ' ')
,  '{', ' '),  '}', ' '),  '$', ' '),  '%', ' ')
,  '           ', ' ')
, '           ', ' ')
, '          ', ' ')
, '         ', ' ')
, '        ', ' ')
, '       ', ' ')
, '      ', ' ')
, '     ', ' ')
, '    ', ' ')
, '   ', ' ')
, '  ', ' ')
,  ';', '')
)
)
AS "Station Name",
UPPER(r.GIS_COUNTY_NAME)                AS "County",
UPPER(
CASE 
  WHEN r.SECONDARY_TYPE_NAME LIKE 'Canal%' then 'Canal' 
  WHEN r.SECONDARY_TYPE_NAME = 'Spring Run' then 'Spring' 
  WHEN r.SECONDARY_TYPE_NAME = 'Channelized Stream' then 'River/Stream' 
ELSE r.SECONDARY_TYPE_NAME  END) AS "Secondary Resource Type",
ROUND(r.DEP_DECIMAL_DEGREE_LAT_NBR, 6)  AS "Lat",
ROUND(r.DEP_DECIMAL_DEGREE_LONG_NBR, 6) AS "Lon",
r.ORGANIZATION_ID                       AS "ORG ID"
  
FROM WIN_SNAP.WH1R_MONLOC_WELL_ORG_ALL_MV r
FULL JOIN WIN_SNAP.WH2R_RESULT_ACTVTY_ORG_LOCS
ON WIN_SNAP.WH2R_RESULT_ACTVTY_ORG_LOCS.MONITORING_LOC_ID = r.MONITORING_LOC_ID
WHERE r.ORGANIZATION_ID  IN ('21FLCEN', '21FLWPB', '21FLFTM', '21FLTPA', '21FLA', '21FLTLHR', '21FLPNS')
--WHERE r.ORGANIZATION_ID  IN ( '21FLTPA' )
--AND r.ORGANIZATION_ID  IN ( '21FLTPA' )
AND (
     WIN_SNAP.WH2R_RESULT_ACTVTY_ORG_LOCS.PROJECT_IDS_TEXT LIKE '%BLOOM-RESP%'
  or WIN_SNAP.WH2R_RESULT_ACTVTY_ORG_LOCS.PROJECT_IDS_TEXT LIKE '%HAB-ROUTNE%'
  or r.MONITORING_LOC_ID LIKE '%HAB-SW%'
  or r.MONITORING_LOC_ID LIKE '%HAB-SE%'
  or r.MONITORING_LOC_ID LIKE '%HAB-SD%'
  or r.MONITORING_LOC_ID LIKE '%HAB-CE%'
  or r.MONITORING_LOC_ID LIKE '%HAB-NE%'
  or r.MONITORING_LOC_ID LIKE '%HAB-NW%'
  or r.MONITORING_LOC_ID LIKE '%HAB-TLH%'
  
  or r.MONITORING_LOC_ID LIKE '%SWHAB%'
  or r.MONITORING_LOC_ID LIKE '%SEHAB%'
  or r.MONITORING_LOC_ID LIKE '%SDHAB%'
  or r.MONITORING_LOC_ID LIKE '%CEHAB%'
  or r.MONITORING_LOC_ID LIKE '%NEHAB%'
  or r.MONITORING_LOC_ID LIKE '%NWHAB%'
  or r.MONITORING_LOC_ID LIKE '%TLHHAB%' 

  or r.MONITORING_LOC_ID LIKE '%HABSW%'
  or r.MONITORING_LOC_ID LIKE '%HABSE%'
  or r.MONITORING_LOC_ID LIKE '%HABSD%'
  or r.MONITORING_LOC_ID LIKE '%HABCE%'
  or r.MONITORING_LOC_ID LIKE '%HABNE%'
  or r.MONITORING_LOC_ID LIKE '%HABNW%'
  or r.MONITORING_LOC_ID LIKE '%HABTLH%'
  
  or r.MONITORING_LOC_ID LIKE '%SW-HAB%'
  or r.MONITORING_LOC_ID LIKE '%SE-HAB%'
  or r.MONITORING_LOC_ID LIKE '%SD-HAB%'
  or r.MONITORING_LOC_ID LIKE '%CE-HAB%'
  or r.MONITORING_LOC_ID LIKE '%NE-HAB%'
  or r.MONITORING_LOC_ID LIKE '%NW-HAB%'
  or r.MONITORING_LOC_ID LIKE '%TLH-HAB%'
  )
ORDER BY r.ORGANIZATION_ID, r.MONITORING_LOC_ID
;

--810 5/15/2023