SELECT
l.areasymbol, l.areaname, mu.musym, mu.muname, co.compname, mu.museq,
CASE WHEN como.flodfreqcl = 'None' THEN 0
WHEN como.flodfreqcl = 'Very Rare' THEN 1
WHEN como.flodfreqcl = 'Rare' THEN 2
WHEN como.flodfreqcl = 'Occasional' THEN 3
WHEN como.flodfreqcl = 'Frequent' THEN 4
WHEN como.flodfreqcl = 'Very Frequent' THEN 5
ELSE Null
END AS flood, como.flodfreqcl,
CASE WHEN como.floddurcl = 'Extremely brief (0.1 to 4 hours)' THEN 0
WHEN como.floddurcl = 'Very brief (4 to 48 hours)' THEN 1
WHEN como.floddurcl = 'Brief (2 to 7 days)' THEN 2
WHEN como.floddurcl = 'Long (7 to 30 days)' THEN 3
WHEN como.floddurcl = 'Very long (more than 30 days)' THEN 4
ELSE Null
END AS flooddur, como.floddurcl,
CASE WHEN como.pondfreqcl = 'None' THEN 0
WHEN como.pondfreqcl = 'Rare' THEN 2
WHEN como.pondfreqcl = 'Occasional' THEN 3
WHEN como.pondfreqcl = 'Frequent' THEN 4
ELSE Null
END AS Pond, como.pondfreqcl,
CASE WHEN como.ponddurcl = 'Very brief (4 to 48 hours)' THEN 1
WHEN como.ponddurcl = 'Brief (2 to 7 days)' THEN 2
WHEN como.ponddurcl = 'Long (7 to 30 days)' THEN 3
WHEN como.ponddurcl = 'Very long (more than 30 days)' THEN 4
ELSE Null
END AS ponddur, como.ponddurcl, mu.mukey
INTO #FloodPond1
FROM sacatalog AS sc
INNER JOIN legend AS l ON l.areasymbol = sc.areasymbol AND l.areasymbol LIKE
'FL%'
INNER JOIN mapunit AS mu ON mu.lkey = l.lkey
LEFT OUTER JOIN component AS co ON co.mukey = mu.mukey
LEFT OUTER JOIN comonth AS como ON como.cokey = co.cokey
WHERE co.majcompflag = 'yes'
SELECT areasymbol, areaname, musym, muname, MAX(Flood) AS MstLmtFlood, MAX(Flooddur) AS MstLmtFlooddur,
MAX(Pond) AS MstLmtPond, MAX(Ponddur) AS MstLmtPonddur, mukey AS MUKEY, museq
INTO #FloodPond2
FROM #FloodPond1
GROUP BY areasymbol, areaname, musym, muname, mukey, museq
SELECT areasymbol, areaname, musym, muname,
CASE WHEN MstLmtFlood = 0 THEN 'None'
WHEN MstLmtFlood = 1 THEN 'Very Rare'
WHEN MstLmtFlood = 2 THEN 'Rare'
WHEN MstLmtFlood = 3 THEN 'Occasional'
WHEN MstLmtFlood = 4 THEN 'Frequent'
WHEN MstLmtFlood = 5 THEN 'Very Frequent'
ELSE Null
END AS MstLmtFloodCl,
CASE WHEN MstLmtFlooddur = 0 THEN 'Extremely brief'
WHEN MstLmtFlooddur = 1 THEN 'Very Brief'
WHEN MstLmtFlooddur = 2 THEN 'Brief'
WHEN MstLmtFlooddur = 3 THEN 'Long'
WHEN MstLmtFlooddur = 4 THEN 'Very Long'
ELSE Null
END AS MstLmtFlooddurCl,
CASE WHEN MstLmtPond = 0 THEN 'None'
WHEN MstLmtPond = 2 THEN 'Rare'
WHEN MstLmtPond = 3 THEN 'Occasional'
WHEN MstLmtPond = 4 THEN 'Frequent'
ELSE Null
END AS MstLmtPondCl,
CASE WHEN MstLmtPonddur = 1 THEN 'very brief'
WHEN MstLmtPonddur = 2 THEN 'brief'
WHEN MstLmtPonddur = 3 THEN 'long'
WHEN MstLmtPonddur = 4 THEN 'very long'
ELSE Null
END AS MstLmtPonddurCl, mukey AS MUKEY
FROM #FloodPond2
ORDER BY areasymbol, museq
DROP TABLE #FloodPond1
DROP TABLE #FloodPond2