SELECT l.areasymbol AS county, l.areaname, laoverlap.areasymbol AS mlra, l.lkey, m.musym, m.muname, m.mukey,
m.muacres AS mapunitacres, muaoverlap.areaovacres AS mLraacres, compname, slope_l, slope_r, slope_h, slopelenusle_r, localphase, comppct_r, compkind, majcompflag, c.cokey, drainagecl, hydgrp,
CASE WHEN c.nirrcapscl IS NULL THEN c.nirrcapcl ELSE c.nirrcapcl + c.nirrcapscl END AS capclass, runoff, tfact, farmlndcl, frostact, weg, wei, foragesuitgrpid, aws0100wta, aws0150wta, taxgrtgroup, taxsubgrp, taxpartsize,
(SELECT CASE WHEN MIN(soimoistdept_r) IS NULL THEN '>200' ELSE CAST(MIN(soimoistdept_r) AS VARCHAR) END
FROM component
LEFT OUTER JOIN comonth ON comonth.cokey = component.cokey
LEFT OUTER JOIN cosoilmoist ON comonth.comonthkey = cosoilmoist.comonthkey
WHERE component.cokey = c.cokey AND soimoiststat = 'Wet'
and ((taxtempregime IN ('Cryic', 'Pergelic') AND comonth.month IN ('July', 'August')) OR (taxtempregime IN ('Frigid', 'Mesic', 'Isofrigid') AND
comonth.month IN ('May', 'June', 'July', 'August', 'September')) OR (taxtempregime IN ('Thermic', 'Hyperthermic')
AND comonth.month IN ('April', 'May', 'June', 'July', 'August', 'September', 'October')) OR (taxtempregime IN ('Isothermic', 'Isohyperthermic', 'Isomesic')
AND comonth.month IN ('March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November')))) AS mingswatertable_r,
(SELECT top 1 flodfreqcl
FROM comonth, MetadataDomainMaster AS dm, MetadataDomainDetail AS dd
WHERE comonth.cokey = c.cokey AND flodfreqcl = ChoiceLabel AND DomainName = 'flooding_frequency_class' AND dm.DomainID = dd.DomainID ORDER BY choicesequence DESC) AS flodfreq ,
(SELECT top 1 floddurcl
FROM comonth, MetadataDomainMaster AS dm, MetadataDomainDetail AS dd
WHERE comonth.cokey = c.cokey AND flodfreqcl = ChoiceLabel AND DomainName = 'flooding_frequency_class' AND dm.DomainID = dd.DomainID
ORDER BY choicesequence DESC) AS floddur,
(SELECT top 1 pondfreqcl
FROM comonth, MetadataDomainMaster AS dm, MetadataDomainDetail AS dd
WHERE comonth.cokey = c.cokey AND pondfreqcl = ChoiceLabel AND DomainName = 'ponding_frequency_class' AND dm.DomainID = dd.DomainID
ORDER BY choicesequence DESC) AS pondfreq,
(SELECT top 1 ponddurcl
FROM comonth, MetadataDomainMaster AS dm, MetadataDomainDetail AS dd
WHERE comonth.cokey = c.cokey AND pondfreqcl = ChoiceLabel AND DomainName = 'ponding_frequency_class' AND dm.DomainID = dd.DomainID
ORDER BY choicesequence DESC) AS ponddur,
(SELECT TOP 1 reskind
FROM component
LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey
WHERE component.cokey = c.cokey AND reskind is not null) AS restrictionkind,
(SELECT CASE WHEN MIN(resdept_l) IS NULL THEN '>200' ELSE CAST(MIN(resdept_l) AS VARCHAR) END
FROM component
LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey
WHERE component.cokey = c.cokey AND reskind is not null) AS restrictiondepthl,
(SELECT CASE WHEN MIN(resdept_r) IS NULL THEN '>200' ELSE CAST(MIN(resdept_r) AS VARCHAR) END
FROM component
LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey
WHERE component.cokey = c.cokey AND reskind is not null) AS restrictiondepthr,
(SELECT CASE WHEN MIN(resdept_h) IS NULL THEN '>200' ELSE CAST(MIN(resdept_h) AS VARCHAR) END
FROM component
LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey
WHERE component.cokey = c.cokey AND reskind is not null) AS restrictiondepthh
, hzdept_r, hzdepb_r, h.chkey, sandtotal_r, silttotal_r, claytotal_r, kwfact, kffact
, ksat_l, ksat_r, ksat_h, dbthirdbar_r, awc_l, awc_r, awc_h, dbovendry_r, wthirdbar_r
, wfifteenbar_r, om_l, om_r, om_h, sar_l, sar_r, sar_h, caco3_l, caco3_r, caco3_h, gypsum_l
, gypsum_r, gypsum_h, lep_l, lep_r, lep_h
, CASE WHEN ecec_l IS NULL THEN cec7_l ELSE ecec_l END AS cec_l
, CASE WHEN ecec_r IS NULL THEN cec7_r ELSE ecec_r END AS cec_r
, CASE WHEN ecec_h IS NULL THEN cec7_h ELSE ecec_h END AS cec_h
, CASE WHEN ph01mcacl2_l IS NULL THEN ph1to1h2o_l ELSE ph01mcacl2_l END AS ph_l
, CASE WHEN ph01mcacl2_r IS NULL THEN ph1to1h2o_r ELSE ph01mcacl2_r END AS ph_r
, CASE WHEN ph01mcacl2_h IS NULL THEN ph1to1h2o_h ELSE ph01mcacl2_h END AS ph_h,
(SELECT CASE WHEN MAX(ec_r) IS NULL THEN '0' ELSE CAST(MAX(ec_r) AS VARCHAR) END
FROM component
LEFT OUTER JOIN chorizon ON component.cokey=chorizon.cokey
WHERE component.cokey = c.cokey) AS max_ec,
(SELECT top 1 texture
FROM chorizon
LEFT OUTER JOIN chtexturegrp ON chorizon.chkey = chtexturegrp.chkey
WHERE rvindicator = 'yes' AND chorizon.chkey = h.chkey
ORDER BY rvindicator DESC) AS texture
, frag3to10_l, frag3to10_r, frag3to10_h, fraggt10_l, fraggt10_r, fraggt10_h,
(SELECT SUM(fragvol_r) FROM chfrags WHERE chkey = h.chkey) AS fragvol
FROM legend AS l
INNER JOIN laoverlap
INNER JOIN mapunit AS m ON laoverlap.lkey = m.lkey ON l.lkey = m.lkey AND laoverlap.areatypename LIKE 'MLRA' AND l.lkey = laoverlap.lkey
INNER JOIN muaoverlap ON m.mukey = muaoverlap.mukey AND laoverlap.lareaovkey = muaoverlap.lareaovkey
LEFT OUTER JOIN component AS c ON m.mukey = c.mukey
LEFT OUTER JOIN chorizon AS h ON c.cokey = h.cokey
LEFT OUTER JOIN muaggatt AS ma ON m.mukey = ma.mukey
WHERE l.areasymbol LIKE
'NC003'
AND majcompflag = 'Yes' AND hzdept_r = 0
ORDER BY l.areasymbol, musym, comppct_r DESC