SELECT areasymbol, musym, mapunit.mukey, muname, compname, comppct_r, majcompflag, cointerp.mrulename,
cointerp.ruledepth, interphr, interphrc, rulename, component.cokey
INTO #ratings
FROM legend INNER JOIN mapunit ON legend.lkey = mapunit.lkey
INNER JOIN component ON mapunit.mukey = component.mukey
INNER JOIN cointerp ON component.cokey = cointerp.cokey
WHERE legend.areasymbol LIKE
'KS169'
AND ruledepth = 0
AND majcompflag = 'Yes'
AND mrulename LIKE
'DHS - Catastrophic Mortality, Large Animal Disposal, Trench'
AND cointerp.seqnum = 0
ORDER BY areasymbol, mukey, comppct_r DESC, cokey, cointerp.mrulename, ruledepth, interphr DESC, rulename
SELECT areasymbol, musym, mapunit.mukey, mapunit.muname, compname, comppct_r, majcompflag, cointerp.mrulename,
cointerp.ruledepth, interphr, interphrc, rulename, component.cokey
INTO #basement
FROM legend INNER JOIN mapunit ON legend.lkey = mapunit.lkey
INNER JOIN component ON mapunit.mukey = component.mukey
INNER JOIN cointerp ON component.cokey = cointerp.cokey
WHERE legend.areasymbol LIKE 'KS169'
AND interphr <> 0
AND cointerp.seqnum >0
AND mrulename LIKE
'DHS - Catastrophic Mortality, Large Animal Disposal, Trench'
ORDER BY mapunit.mukey, comppct_r DESC, cokey, cointerp.mrulename, ruledepth, interphr DESC, rulename
SELECT DISTINCT mukey, muname, cokey, compname, comppct_r, interphr, SUBSTRING( ( SELECT ( '; ' + interphrc)
FROM #basement t2
WHERE t1.cokey = t2.cokey
ORDER BY t1.cokey, t2.cokey
FOR XML PATH('') ), 3, 1000) AS restrictions
INTO #tempbase FROM #basement t1
GROUP BY mukey, muname, comppct_r, cokey, compname, interphr, interphrc
ORDER BY mukey, muname, comppct_r DESC, cokey, compname, interphr DESC
SELECT areasymbol, musym, r.mukey, r.muname, r.cokey, r.compname, r.comppct_r, majcompflag, mrulename, r.interphr, interphrc, restrictions
INTO #final
FROM #ratings r
LEFT OUTER JOIN #tempbase t ON r.cokey=t.cokey
ORDER BY mukey, muname, comppct_r DESC, cokey, compname
SELECT DISTINCT areasymbol AS Area_symbol, musym AS Map_unit_symbol, mukey AS MUKEY, muname AS Map_unit_name, cokey AS COKEY,
compname AS Component_name, comppct_r AS Component_percentage, majcompflag, mrulename AS Rule_name, CAST(CONVERT(DECIMAL(10,1),interphr) AS nvarchar) AS Rating,
interphrc + ': ' + ISNULL(restrictions, '') AS Interpretation_class
FROM #final
ORDER BY mukey, muname, comppct_r DESC, cokey, compname
DROP TABLE #basement
DROP TABLE #tempbase
DROP TABLE #ratings
DROP TABLE #final