SELECT LEFT((l.areasymbol), 2) AS state,
lo.areasymbol, l.areaname, airtempa_l, airtempa_r, airtempa_h, ffd_l, ffd_r, ffd_h, map_l, map_r, map_h, mu.mukey,
mu.musym, mu.muname, mu.muacres, c.cokey, c.comppct_r, c.compname, localphase,
CASE WHEN nirrcapscl IS NULL THEN nirrcapcl ELSE nirrcapcl + nirrcapscl END AS capclass,
c.slope_r, hydgrp, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize,
(SELECT TOP 1 taxminalogy FROM cotaxfmmin WHERE cotaxfmmin.cokey=c.cokey) AS mineralogy,
taxtempregime, hydricrating,
(SELECT TOP 1 cocropyld.nonirryield_r FROM cocropyld WHERE c.cokey=cocropyld.cokey AND cropname LIKE 'Pasture') AS PastureYLD,
(SELECT TOP 1 coecoclass.ecoclassid FROM component LEFT OUTER JOIN coecoclass ON component.cokey =
coecoclass.cokey WHERE coecoclass.cokey = c.cokey AND coecoclass.ecoclassref LIKE 'Ecological Site Description
Database' ORDER BY ecoclassid) AS ecositeID,
(SELECT TOP 1 coecoclass.ecoclassname FROM component LEFT OUTER JOIN coecoclass ON component.cokey =
coecoclass.cokey WHERE coecoclass.cokey = c.cokey AND coecoclass.ecoclassref LIKE 'Ecological Site Description
Database' ORDER BY ecoclassid) AS ecositename,
constreeshrubgrp,
(SELECT TOP 1 coecoclass.ecoclassid FROM component INNER JOIN coecoclass ON component.cokey = coecoclass.cokey
AND ecoclasstypename LIKE 'Forage Suitability Groups' WHERE coecoclass.cokey = c.cokey ) AS foragesuitgroupid,
(SELECT TOP 1 coecoclass.ecoclassname FROM component INNER JOIN coecoclass ON component.cokey = coecoclass.cokey
AND ecoclasstypename LIKE 'Forage Suitability Groups' WHERE coecoclass.cokey = c.cokey ) AS foragesuitgroupname,
foragesuitgrpid, rsprod_l, rsprod_r, rsprod_h,
(SELECT TOP 1 cp.fprod_l FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE
component.cokey = c.cokey ORDER BY cp.fprod_l DESC) AS ForestProd_l,
(SELECT TOP 1 cp.fprod_r FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE
component.cokey = c.cokey ORDER BY cp.fprod_r DESC) AS ForestProd_r,
(SELECT TOP 1 cp.fprod_h FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE
component.cokey = c.cokey ORDER BY cp.fprod_h DESC) AS ForestProd_h,
(SELECT CASE WHEN min(resdept_r) IS NULL THEN 999 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 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 restrkind,
(SELECT TOP 1 reshard FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey
WHERE component.cokey = c.cokey AND reskind IS NOT NULL) AS restrhard,
(SELECT CASE WHEN MIN(soimoistdept_r) IS NULL THEN 999 ELSE
CAST(MIN(soimoistdept_r) AS VARCHAR) END FROM component LEFT OUTER JOIN
comonth LEFT OUTER JOIN cosoilmoist ON comonth.comonthkey =
cosoilmoist.comonthkey ON comonth.cokey = component.cokey 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 CASE WHEN MAX(soimoistdept_r) IS NULL THEN 999 ELSE
CAST(MAX(soimoistdept_r) AS VARCHAR) END FROM component LEFT OUTER JOIN
comonth LEFT OUTER JOIN cosoilmoist ON comonth.comonthkey =
cosoilmoist.comonthkey ON comonth.cokey = component.cokey 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 maxgswatertable_r,
(SELECT TOP 1 flodfreqcl FROM comonth, MetadataDomainMaster dm,
MetadataDomainDetail 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 pondfreqcl FROM comonth, MetadataDomainMaster dm,
MetadataDomainDetail dd WHERE comonth.cokey = c.cokey AND pondfreqcl =
ChoiceLabel AND DomainName = 'ponding_frequency_class' AND dm.DomainID =
dd.DomainID ORDER BY choicesequence DESC) AS ponding_frequency,
(SELECT TOP 1 copmgrp.pmgroupname FROM copmgrp WHERE c.cokey = copmgrp.cokey
AND copmgrp.rvindicator='yes') AS parent_material,
(SELECT TOP 1 cogeomordesc.geomfname FROM cogeomordesc WHERE c.cokey =
cogeomordesc.cokey AND cogeomordesc.rvindicator='yes' AND
cogeomordesc.geomftname = 'Landform') AS landform, drainagecl, hzname, hzdept_r, hzdepb_r, texture AS
surface_texture,
CASE WHEN (100-sieveno10_r) = 0 THEN '' ELSE (100-sieveno10_r) END AS sur_hor_sieve_frags,
(SELECT SUM(cf.fragvol_r) AS fragvol FROM chfrags cf WHERE cf.chkey = ch.chkey ) AS surf_fragvol,
(SELECT SUM(sfragcov_r) FROM cosurffrags WHERE c.cokey = cosurffrags.cosurffragskey ) AS surf_cov_fragvol,
om_r AS surf_om_r,
(SELECT MIN(om_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100om,
(SELECT MAX(om_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100om,
(SELECT 1-(dbthirdbar_r/2.65) FROM chorizon WHERE chorizon.cokey=c.cokey AND hzdept_r = 0) AS surface_porosity,
dbthirdbar_r AS surf_dbthirdbar_r,
(SELECT MIN(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100dbthirdbar_r,
(SELECT MAX(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100dbthirdbar_r,
(SELECT MIN(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS min150dbthirdbar_r,
(SELECT MAX(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS max150dbthirdbar_r,
(SELECT MIN(awc_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100awc,
(SELECT MAX(awc_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100awc,
(SELECT MIN(awc_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS min150awc,
(SELECT MAX(awc_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS max150awc,
lep_r AS surf_lep_r,
(SELECT MIN(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100lep,
(SELECT MAX(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100lep,
(SELECT SUM(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS sum100lep,
ksat_r AS surf_ksat_r,
(SELECT MIN(ksat_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100Ksat,
(SELECT MAX(ksat_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100Ksat,
(SELECT ph1to1h2o_r FROM chorizon WHERE hzdept_r = 0 AND c.cokey=chorizon.cokey ) AS surf_ph_r,
(SELECT MAX(ph1to1h2o_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_max_ph_r,
(SELECT MIN(ph1to1h2o_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_min_ph_r,
(SELECT ph01mcacl2_r FROM chorizon WHERE hzdept_r = 0 AND c.cokey=chorizon.cokey ) AS surf_CA_ph_r,
(SELECT MAX(ph01mcacl2_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_max_CA_ph_r,
(SELECT CASE WHEN SUM(((hzdepb_r-hzdept_r)*awc_r)) IS NULL THEN '0' ELSE CAST(sum(((hzdepb_r-hzdept_r)*awc_r)) AS VARCHAR)
END FROM chorizon WHERE chorizon.cokey = c.cokey) AS soil_profile_AWS,
(SELECT aws0150wta FROM muaggatt WHERE mu.mukey= muaggatt.mukey) AS mapunitAWS150, ec_r AS surf_ec_r,
(SELECT CASE WHEN SUM(ec_r) IS NULL THEN '0' ELSE CAST(SUM(ec_r) AS VARCHAR ) END FROM chorizon WHERE
chorizon.cokey = c.cokey) AS soil_sum_EC_r,
(SELECT MIN(ec_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100EC,
(SELECT MAX(ec_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100EC,
(SELECT MAX(ec_r) FROM chorizon WHERE chorizon.cokey=c.cokey) AS maxEC,
caco3_r AS surf_caco3_r,
(SELECT CASE WHEN SUM(caco3_r) IS NULL THEN '0' ELSE CAST(SUM(caco3_r) AS VARCHAR ) END FROM chorizon WHERE
chorizon.cokey = c.cokey) AS soil_sum_CaCO3_r,
sar_r AS surf_sar_r,
(SELECT MIN(sar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100sar,
(SELECT MAX(sar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100sar,
gypsum_r AS surf_gypsum_r,
(SELECT MIN(gypsum_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100gypsum,
(SELECT MAX(gypsum_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100gypsum
FROM legend AS l
INNER JOIN laoverlap AS lo ON l.lkey=lo.lkey AND l.areasymbol <> 'US'
INNER JOIN muaoverlap AS mo ON mo.lareaovkey = lo.lareaovkey AND lo.areatypename='mlra' AND lo.areasymbol LIKE
'74'
INNER JOIN mapunit AS mu ON mu.mukey = mo.mukey
INNER JOIN component AS c ON c.mukey = mu.mukey AND compkind = 'series'
INNER JOIN chorizon AS ch ON ch.cokey = c.cokey AND hzdept_r = 0
INNER JOIN chtexturegrp AS ct ON ch.chkey=ct.chkey AND ct.rvindicator = 'yes'
ORDER BY l.areasymbol, mu.musym