SELECT LEFT((l.areasymbol), 2) AS state, lo.areasymbol, l.areaname, mu.mukey, mu.musym, mu.muname, mukind, mu.muacres, mo.areaovacres, c.majcompflag, c.comppct_r,
c.compname, compkind, (mu.muacres*c.comppct_r/100) AS compacres, localphase, slope_l, slope_r, slope_h, CASE WHEN nirrcapscl IS NULL THEN
nirrcapcl ELSE nirrcapcl + nirrcapscl END AS capclass, drainagecl, hydgrp, hydricrating, (SELECT TOP 1 hydriccriterion FROM cohydriccriteria
WHERE c.cokey = cohydriccriteria.cokey) AS criteria, corsteel, corcon, flecolcomnum,
(SELECT TOP 1 coecoclass.ecoclassid FROM component LEFT OUTER JOIN coecoclass ON component.cokey = coecoclass.cokey
WHERE ecoclasstypename NOT IN ('NRCS Rangeland Site', 'NRCS Forestland Site') AND coecoclass.cokey = c.cokey) AS ecosite
, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxtempregime, taxpartsize
, (SELECT TOP 1 copmgrp.pmgroupname FROM copmgrp WHERE c.cokey = copmgrp.cokey AND copmgrp.rvindicator='yes') AS pm
, (SELECT TOP 1 cogeomordesc.geomfname FROM cogeomordesc WHERE c.cokey = cogeomordesc.cokey AND cogeomordesc.rvindicator='yes' AND cogeomordesc.geomftname = 'Landform') AS landform
, (SELECT CASE WHEN MIN(soimoistdept_r) IS NULL THEN '200' ELSE (MIN(soimoistdept_r) ) 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 CASE WHEN MAX(soimoistdept_r) IS NULL THEN '200' ELSE (MAX(soimoistdept_r)) 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 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 floddurcl FROM comonth, MetadataDomainMaster dm, MetadataDomainDetail dd WHERE comonth.cokey = c.cokey AND floddurcl
= ChoiceLabel AND DomainName = 'flooding_duration_class' AND dm.DomainID = dd.DomainID order by choicesequence DESC) AS floddur, (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 pondfreq, (SELECT TOP 1 ponddurcl FROM
comonth, MetadataDomainMaster dm, MetadataDomainDetail dd WHERE comonth.cokey = c.cokey AND ponddurcl = ChoiceLabel AND DomainName =
'ponding_duration_class' AND dm.DomainID = dd.DomainID ORDER BY choicesequence DESC) AS ponddur, (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 restrictiondept, (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 restriction, c.cokey, hzname, hzdept_r,
hzdepb_r, (hzdepb_r-hzdept_r) AS thickness, (SELECT CASE WHEN SUM(cf.fragvol_r) IS NULL THEN '0' ELSE CAST(SUM(cf.fragvol_r) AS VARCHAR) END
FROM chfrags cf WHERE cf.chkey = ch.chkey) AS fragvol
, texture, sandtotal_r, sandvc_r, sandco_r, sandmed_r, sandfine_r, sandvf_r, silttotal_r, claytotal_r, om_r, awc_r, ksat_r, ph1to1h2o_r
, ph01mcacl2_r, caco3_r,cec7_r,ecec_r,ec_r,sar_r,ch.chkey
INTO #temp
FROM legend l
INNER JOIN laoverlap lo ON l.lkey=lo.lkey AND l.areasymbol <> 'US'
INNER JOIN muaoverlap mo ON mo.lareaovkey = lo.lareaovkey AND lo.areatypename='mlra' AND lo.areasymbol LIKE
'116C'
INNER JOIN mapunit mu ON mu.mukey = mo.mukey
LEFT OUTER JOIN component c ON c.mukey = mu.mukey AND compkind = 'series'
LEFT OUTER JOIN chorizon ch ON ch.cokey = c.cokey
LEFT OUTER JOIN chtexturegrp ct ON ch.chkey=ct.chkey AND ct.rvindicatOR = 'yes'
ORDER BY l.areasymbol, mu.musym
SELECT state, #temp.areasymbol, areaname, mukey, musym, muname, mukind, muacres, majcompflag, comppct_r, compname,
compkind, compacres, localphase, slope_l, slope_r, slope_h, capclass, drainagecl, hydgrp, hydricrating, criteria, corsteel, corcon,
ecosite, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxtempregime, taxpartsize, pm, landform, mingswatertable_r,
maxgswatertable_r, flodfreq, floddur, pondfreq, ponddur,
CASE WHEN (restrictiondept) = '0' THEN '1' ELSE restrictiondept END AS restrictiondepth, restriction, cokey, hzname, hzdept_r, hzdepb_r,
thickness, fragvol, texture, sandtotal_r, sandvc_r, sandco_r, sandmed_r, sandfine_r, sandvf_r, silttotal_r, claytotal_r, om_r, awc_r, ksat_r
, ph1to1h2o_r, ph01mcacl2_r, caco3_r, cec7_r, ecec_r, ec_r, sar_r, chkey
INTO #temp2
FROM #temp
WHERE hzdept_r = 0
SELECT mukey, cokey, chkey, thickness, restrictiondept, corcon, corsteel,
CASE WHEN sandtotal_r IS NULL THEN '0' ELSE (sandtotal_r*thickness) END AS sand_r,
CASE WHEN sandvc_r IS NULL THEN '0' ELSE (sandvc_r*thickness) END AS vcos_r,
CASE WHEN sandco_r IS NULL THEN '0' ELSE (sandco_r*thickness) END AS cos_r,
CASE WHEN sandmed_r IS NULL THEN '0' ELSE (sandmed_r*thickness) END AS meds_r,
CASE WHEN sandfine_r IS NULL THEN '0' ELSE (sandfine_r*thickness) END AS fines_r,
CASE WHEN sandvf_r IS NULL THEN '0' ELSE (sandvf_r*thickness) END AS vfines_r,
CASE WHEN silttotal_r IS NULL THEN '0' ELSE (silttotal_r*thickness) END AS silt_r,
CASE WHEN claytotal_r IS NULL THEN '0' ELSE (claytotal_r*thickness) END AS clay_r,
CASE WHEN om_r IS NULL THEN '0' ELSE (om_r*thickness) END AS th_om_r,
CASE WHEN awc_r IS NULL THEN '0' ELSE (awc_r*thickness) END AS th_awc_r,
CASE WHEN ksat_r IS NULL THEN '0' ELSE (ksat_r*thickness) END AS th_ksat_r,
CASE WHEN ph1to1h2o_r IS NULL THEN '0' ELSE (ph1to1h2o_r*thickness) END AS th_ph1to1h2o_r,
CASE WHEN ph01mcacl2_r IS NULL THEN '0' ELSE (ph01mcacl2_r*thickness) END AS th_ph01mcacl2_r,
CASE WHEN caco3_r IS NULL THEN '0' ELSE (caco3_r*thickness) END AS th_caco3_r,
CASE WHEN cec7_r IS NULL THEN '0' ELSE (cec7_r*thickness) END AS th_cec7_r,
CASE WHEN ecec_r IS NULL THEN '0' ELSE (ecec_r*thickness) END AS th_ecec_r,
CASE WHEN ec_r IS NULL THEN '0' ELSE (ec_r*thickness) END AS th_ec_r,
CASE WHEN sar_r IS NULL THEN '0' ELSE (sar_r*thickness) END AS th_sar_r
INTO #sand
FROM #temp
ORDER BY mukey, cokey, chkey
SELECT mukey, cokey,
CAST(SUM(sand_r) AS FLOAT(2)) AS sum_sand_r,
CAST(SUM(vcos_r) AS FLOAT(2)) AS sum_vcos_r,
CAST(SUM(cos_r) AS FLOAT(2)) AS sum_cos_r,
CAST(SUM(meds_r) AS FLOAT(2)) AS sum_meds_r,
CAST(SUM(fines_r) AS FLOAT(2)) AS sum_fines_r,
CAST(SUM(vfines_r) AS FLOAT(2)) AS sum_vfines_r,
CAST(SUM(silt_r) AS FLOAT(2)) AS sum_silt_r,
CAST(SUM(clay_r) AS FLOAT(2)) AS sum_clay_r,
CAST(SUM(th_om_r) AS FLOAT(2)) AS sum_om_r,
CAST(SUM(th_awc_r) AS FLOAT(2)) AS sum_awc_r,
CAST(SUM(th_ksat_r) AS FLOAT(2)) AS sum_ksat_r,
CAST(SUM(th_ph1to1h2o_r) AS FLOAT(2)) AS sum_ph1to1h2o_r,
CAST(SUM(th_ph01mcacl2_r) AS FLOAT(2)) AS sum_ph01mcacl2_r,
CAST(SUM(th_caco3_r) AS FLOAT(2)) AS sum_caco3_r,
CAST(SUM(th_cec7_r) AS FLOAT(2)) AS sum_cec7_r,
CAST(SUM(th_ecec_r) AS FLOAT(2)) AS sum_ecec_r,
CAST(SUM(th_ec_r) AS FLOAT(2)) AS sum_ec_r,
CAST(SUM(th_sar_r) AS FLOAT(2)) AS sum_sar_r
INTO #vcsand
FROM #sand
GROUP BY mukey, cokey
ORDER BY mukey
SELECT #temp2.mukey, #temp2.cokey,
ROUND(sum_sand_r/#temp2.restrictiondepth),2) AS wtavg_sand,
(sum_vcos_r/#temp2.restrictiondepth) AS wtavg_vcos,
(sum_cos_r/#temp2.restrictiondepth) AS wtavg_cos,
(sum_meds_r/#temp2.restrictiondepth) AS wtavg_meds,
(sum_fines_r/#temp2.restrictiondepth) AS wtavg_fines,
(sum_vfines_r/#temp2.restrictiondepth) AS wtavg_vfines,
(sum_silt_r/#temp2.restrictiondepth) AS wtavg_silt,
(sum_clay_r/#temp2.restrictiondepth) AS wtavg_clay,
(sum_om_r/#temp2.restrictiondepth) AS wtavg_om,
(sum_awc_r/#temp2.restrictiondepth) AS wtavg_awc,
(sum_ksat_r/#temp2.restrictiondepth) AS wtavg_ksat,
(sum_ph1to1h2o_r/#temp2.restrictiondepth) AS wtavg_phH2O,
(sum_ph01mcacl2_r/#temp2.restrictiondepth) AS wtavg_phCACL,
(sum_caco3_r/#temp2.restrictiondepth) AS wtavg_caco3,
(sum_cec7_r/#temp2.restrictiondepth) AS wtavg_cec7,
(sum_ecec_r/#temp2.restrictiondepth) AS wtavg_ecec,
(sum_ec_r/#temp2.restrictiondepth) AS wtavg_ec,
(sum_sar_r/#temp2.restrictiondepth) AS wtavg_sar
INTO #FLwtavg
FROM #vcsand
INNER JOIN #temp2 ON #vcsand.cokey = #temp2.cokey
ORDER BY mukey, cokey
SELECT state, #temp2.areasymbol, areaname, #temp2.mukey, musym, muname, mukind, muacres, majcompflag, comppct_r,
compname, compkind, compacres, localphase, slope_l, slope_r, slope_h, capclass, drainagecl, hydgrp, hydricrating, criteria,
corsteel, corcon, ecosite, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxtempregime, taxpartsize, pm, landform,
mingswatertable_r, maxgswatertable_r, flodfreq, floddur, pondfreq, ponddur, restrictiondepth, restriction, #temp2.cokey,
hzname, hzdept_r, hzdepb_r, thickness, fragvol, texture, sandtotal_r
,CAST(wtavg_sAND AS INT) AS sand_wtavg
, sandvc_r
,CAST(wtavg_vcos AS DECIMAL(4,1)) AS vcos_wtavg
, sandco_r
,CAST(wtavg_cos AS DECIMAL(4,1)) AS cos_wtavg
, sandmed_r
,CAST(wtavg_meds AS DECIMAL(4,1)) AS msand_wtavg
, sandfine_r
,CAST(wtavg_fines AS DECIMAL(4,1)) AS fsand_wtavg
, sandvf_r
,CAST(wtavg_vfines AS DECIMAL(4,1)) AS vfsand_wtavg
, silttotal_r
,CAST(wtavg_silt AS INT) AS silt_wtavg
, claytotal_r
,CAST(wtavg_clay AS INT) AS clay_wtavg
, om_r
,CAST(wtavg_om AS DECIMAL(4, 1)) AS om_wtavg
, awc_r
,CAST(wtavg_awc AS DECIMAL(3,2)) AS awc_wtavg
, ksat_r
,CAST(wtavg_ksat AS DECIMAL(7,3)) AS ksat_wtavg
, ph1to1h2o_r
,CAST(wtavg_phH2O AS DECIMAL(3,1)) AS ph_wtavg
, ph01mcacl2_r
,CAST(wtavg_phCACL AS DECIMAL(3,1)) AS phcacl_wtavg
, caco3_r
,CAST(wtavg_caco3 AS DECIMAL(4,1)) AS caco3_wtavg
, cec7_r
,CAST(wtavg_cec7 AS DECIMAL(4,1)) AS cec7_wtavg
, ecec_r
,CAST(wtavg_ecec AS DECIMAL(3,1)) AS ecec_wtavg
, ec_r
,CAST(wtavg_ec AS DECIMAL(3,1)) AS ec_wtavg
, sar_r
,CAST(wtavg_sar AS DECIMAL(3,1)) AS sar_wtavg
, chkey
FROM #temp2
LEFT OUTER JOIN #FLwtavg ON #temp2.cokey = #FLwtavg.cokey
ORDER BY #temp2.areasymbol, #temp2.mukey, #temp2.cokey, #temp2.chkey
DROP TABLE #temp
DROP TABLE #temp2
DROP TABLE #sand
DROP TABLE #vcsand
DROP TABLE #FLwtavg