Aggregate Stability

Introduction

The Aggregate Stability Interpretation provides a ranking based on inherent soil properties. It predicts of the potential suitability of soils and map units. Onsite investigation is needed to validate the interpretations in this table and to confirm the identity of the soil on a given site. The numbers in the value columns range from 0 to 100. The larger the value, the greater the potential suitability. The soil may have additional limitations.

Cropping systems, tillage, and management scenarios vary by location and over time, reflecting choices made by farmers. These factors partially mask inherent soil quality. Except for extreme circumstances, inherent soil quality or inherent soil productivity varies little by location over time for a specific soil (map unit component) identified by NRCS soil surveys. The interpretation reflects a relative comparison of the top 6 inches of soils for aggregate stability. It does not forecast actual aggregate stability because actual values are based on weather conditions, soil health, tillage, management conditions, and other factors.

Scope

• Surface and near-surface horizons, c
• Conventional tillage, f
• Focus on macroaggregates (>250 micron)

Definition of What is Estimated

Aggregate stability is defined as the stability of macroaggregates (1–2 mm in size) against flowing water and is expressed as percent stable aggregates of the less-than-2-mm fraction. It is estimated from the organic matter content, total clay, and sodium adsorption ratio. Aggregate stability values are provided for horizons within the upper 6 inches, but not for sandy and organic surface layers.

Significance

Soil aggregate stability affects soil health and crop production. It is important for stabilizing soil structure, increasing water infiltration, and reducing erosion.

Soil aggregates are the smallest unit of soil structure. They are composed of decaying particulate organic matter, clay particles, microbial products, and fine roots. Aggregates are generally divided into macroaggregates (greater than 250 μm) and microaggregates (less than 250 μm). The size, strength, and stability of aggregates depend upon the stabilizing agents involved. They can be classified as temporary, transient, or persistent. Improved aggregate stability leads to increased water infiltration and storage in the profile, increased aeration, reduced erosion, and soil structure that is more resistant to crusting and compaction. Increases in soil organic carbon improve aggregation and aggregate stability, which protect carbon compounds enmeshed in the aggregates from decomposition, leading to carbon sequestration.

Factors Affecting Soil Aggregation and Aggregate Stability

Inherent Factors

Microaggregation is generally considered to be an inherent property of the soil. Persistent binding agents include highly decomposed, high-molecular-weight organic materials (e.g., humic compounds), polymers, and polyvalent cations (e.g., calcium, aluminum, iron) that have a heterogeneous, non-specific structure. These agents are associated with microaggregation and soil organic carbon (SOC) sequestration. These persistent compounds are found in the interior of aggregates, forming organo-mineral complexes via the polyvalent cations. These agents are long-lasting, and the degree of aggregation formed by them is considered part of the inherent soil properties. Generally, management does not impact soil microaggregation. Soils that have a naturally high content of clay and polyvalent cations are likely to form more microaggregates than those soils with a lower content.

Dynamic Factors

Transient binding agents consist mainly of complex carbohydrates, or polysaccharides, and organic mucilages. As plant residues and compounds extruded by plant roots decompose, bacteria release mucilages that are complex carbon-rich carbohydrates. These carbohydrates serve as binding agents, or “glues,” to which clay particles can be adsorbed and bound together. The polysaccharides are non-humic compounds of high molecular weight and comprise about 20 to 25 percent of the soil humus. They are critical for binding microaggregates together, via polymer and polyvalent cation bridges, to form larger macroaggregates. Although binding with clay particles provides some protection against decomposition, these binding agents generally decompose within a few weeks and need to be continually renewed through actively growing plants, decaying residues, or organic amendments.

Temporary binding agents consist of plant roots, especially fine roots and root hairs, fungal hyphae, and bacterial and algal cells. These agents develop along with plant roots, forming a network that entangles mineral particles, through adsorption, to form macroaggregates. As roots cease to grow, the amount of these temporary agents is reduced. Planting cover crops or perennial plants maintains living roots longer in the soil, thus maintaining and strengthening the aggregates. Tillage reduces the number of roots and the amount of microbial biomass, especially in the surface horizon, thereby potentially weakening the aggregates.

Consequences of Weak Aggregates

The first step in erosion is the breakdown of surface aggregates. Aggregates at the soil surface are weakened if the binding agents degrade at rates exceeding replenishment rates. These aggregates can be broken apart by outside forces. Among the most important of these forces are raindrops, wind, variations in sunlight and temperature, and tillage. Changes in soil chemistry, such as increased sodicity of the soil, can also contribute to aggregate breakdown. As aggregates are broken down, the component particles clog the surface pores, leading to surface sealing and crusting. This process results in decreased water infiltration and increased ponding, runoff, erosion, and sediment transport, both on- and off-site. The extent of the process can be minimized by strengthening aggregates.

Additionally, reducing the size and strength of the aggregates throughout the profile weakens soil structure. The weakened structure is more easily compacted by field operations, especially if the soil is too wet. Poor structure can lead to ponding after rainstorms. Ponding can result in increased evaporation and thereby less water-that might otherwise have been available for crop growth-in the profile.

Aggregation and aggregate strength can be maintained or increased through the implementation of soil health management systems. These systems may include reduced tillage operations (or preferably no tillage operations) and the incorporation of cover crops or a cash crop (such as winter wheat) into the rotation. Growing crops and cover crops that have varied rooting structures improves soil structure, as does maintaining living roots in the soil as long as possible. Studies have shown that plants push into the rhizosphere, via the root system, about 20% of the carbon dioxide that is fixed through photosynthesis. Those carbon compounds can support the soil microbial population, which is critical to soil structure, water infiltration, and nutrient cycling. Any management system that leads to increased soil organic carbon is likely to improve aggregate stability.

Measuring Aggregate Stability

Aggregate stability is determined by a wet sieving technique preceded by vacuum saturation of the 1–2 mm size aggregates as described in USDA-ARS (1966). Stable aggregates are corrected for sand greater than 0.25 mm as follows:

Aggregate stability(%)=\[((wt. of stable aggregates and sand) - (wt. of sand))/((wt. of sample) - (wt. of sand))\]

Rating

Rating Class	Rating Value
Low	<25
Moderate	>= 25 and <50
Moderately High	>=50
High	>=75
Not Rated	Null

Criteria Table

Property	Strong stability	Moderate stability	Moderate stability	Reason
% clay	> 36	21–35	0–20	Lower clay content results in lower aggregate stability.
% OM	> 5	1–5	0–1	Lower organic matter content results in lower aggregate stability.
Suborder / SMR Depth to Water			Aqu- / aquic within 50cm	Shallow depth to water table during growing season results in higher moisture status. Soil aggregates are less stable at higher moisture content.
Fe2O3 (Fed mass %)	> 2	0.5–2	< 0.5	Low content of free iron oxide (esp. with low % OM) results in lower aggregate stability.
ESP %	0 - 4	4 - 10	> 10	High exchangeable sodium percentage results in dispersion of clay and low aggregate stability.
EC (dS/m)	Any (with ESP < 4%)	< 4 (with ESP > 4%)	< 4 (with ESP > 10%)	Low EC (with high ESP) results in dispersion of clay and low aggregate stability.

Soil Script Breakdown

Create AoI Table

CREATE TABLE #AoiTable 
    ( aoiid INT IDENTITY (1,1),
    landunit CHAR(20),
    aoigeom GEOMETRY);

• Create AOI table with polygon geometry. Coordinate system must be WGS1984 (EPSG 4326).

SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.12335160658608 45.959173206572416, -102.13402890980223 45.959218442561564, -102.13386921506947 45.944643788188387, -102.12327175652177 45.944703605814198, -102.12335160658608 45.959173206572416)))', 4326);   
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());  
INSERT INTO #AoiTable ( landunit, aoigeom )  
VALUES ('T9981 Fld3', @aoiGeomFixed); 
SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.1130336443976 45.959162795100383, -102.12335160658608 45.959173206572416, -102.12327175652177 45.944703605814198, -102.1128892282776 45.944710506326032, -102.1130336443976 45.959162795100383)))', 4326);   
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());  
INSERT INTO #AoiTable ( landunit, aoigeom )  
VALUES ('T9981 Fld4', @aoiGeomFixed);

Create summary acres for each landunit

CREATE TABLE #AoiAcres
    ( aoiid INT,
    landunit CHAR(20),
    landunit_acres FLOAT
    );

INSERT INTO #AoiAcres (aoiid, landunit, landunit_acres )
SELECT  aoiid, landunit,
SUM( ROUND( ( ( GEOGRAPHY::STGeomFromWKB(aoigeom.STAsBinary(), 4326 ).STArea() ) / 4046.8564224 ), 3 ) ) AS landunit_acres
FROM #AoiTable
GROUP BY aoiid, landunit;

aoiid	landunit	landunit_acres
1	T9981 Fld3	328.952
2	T9981 Fld4	318.722

Populate intersected soil polygon table with geometry

-- Create intersected soil polygon table with geometry
CREATE TABLE #AoiSoils 
    ( polyid INT IDENTITY (1,1),
    aoiid INT,
    landunit CHAR(20),
    mukey INT,
    soilgeom GEOMETRY
    );

INSERT INTO #AoiSoils (aoiid, landunit, mukey, soilgeom)
SELECT A.aoiid, A.landunit, M.mukey, M.mupolygongeo.STIntersection(A.aoigeom ) AS soilgeom
FROM mupolygon M, #AoiTable A
WHERE mupolygongeo.STIntersects(A.aoigeom) = 1;

Populate soil geometry with landunit attribute

-- Soil geometry with landunits
CREATE TABLE #AoiSoils2 
    ( aoiid INT,
    polyid INT,
    landunit CHAR(20),
    mukey INT,
    poly_acres FLOAT,
    soilgeog GEOGRAPHY
    );

-- Populate Soil geometry with landunit attribute
INSERT INTO #AoiSoils2   
SELECT aoiid, polyid, landunit,  mukey, ROUND((( GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ).STArea() ) / 4046.8564224 ), 3 ) AS poly_acres, GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ) AS soilgeog 
FROM #AoiSoils;

Start of Estimating Aggregate Stability

CREATE TABLE #agg1
(  aoiid INT ,
landunit CHAR(20), 
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag  CHAR(3),
localphase CHAR(60),
 hzname CHAR(20),
 hzdept_r INT,
 hzdepb_r INT,
claytotall FLOAT,
claytotalr FLOAT,
claytotalh FLOAT,
oml  FLOAT ,
 omr FLOAT  ,
omh  FLOAT,
 sar_l FLOAT,
 sar_r FLOAT,
 sar_h FLOAT,
 cec7_l FLOAT,
 cec7_r FLOAT,
 cec7_h FLOAT,
 ec_l FLOAT,
 ec_r FLOAT,
 ec_h FLOAT,
 esp_l FLOAT,
esp_r FLOAT,
esp_h FLOAT, 
tcl CHAR(40),
mu_pct_sum INT,
major_mu_pct_sum INT,
)
;

INSERT INTO #agg1

SELECT DISTINCT 
 MA44.aoiid ,
 MA44.landunit, 
 MA44.mukey,
 MA44.mapunit_acres, 
 MA44.cokey,
 MA44.compname,
 MA44.comppct_r,
 MA44.majcompflag,
 localphase,
 hzname,
 hzdept_r,
 hzdepb_r,
 CASE WHEN claytotal_l > 92 then 92 WHEN claytotal_l < 8 THEN 8 ELSE claytotal_l END AS claytotall,
 CASE WHEN claytotal_r > 92 then 92 ELSE claytotal_r END AS claytotalr,
 CASE WHEN claytotal_h > 92 then 92 ELSE claytotal_h END AS claytotalh,
  FORMAT ( CASE WHEN om_l <0.01 THEN 0.05 WHEN om_l > 17 then 17 ELSE om_l END , '#,###,##0.00') AS oml  ,
  FORMAT (CASE WHEN om_r <0.01 THEN 0.05 WHEN om_r > 17 then 17 ELSE om_r END , '#,###,##0.00') AS omr  ,
  FORMAT (CASE WHEN om_h <0.01 THEN 0.05 WHEN om_h > 17 then 17 ELSE om_h END , '#,###,##0.00') AS omh  ,
 sar_l,
 sar_r,
 sar_h,
 cec7_l,
 cec7_r,
 cec7_h,
 ec_l,
 ec_r,
 ec_h,
  FORMAT (CAST ((100*(-0.0126+0.01475*sar_l))/(1+(-0.0126+0.01475*sar_l)) as float)  , '#,###,##0.00')  as esp_l,
 FORMAT (CAST ((100*(-0.0126+0.01475*sar_r))/(1+(-0.0126+0.01475*sar_r)) as float) , '#,###,##0.00')  as esp_r,
 FORMAT (CAST ((100*(-0.0126+0.01475*sar_h))/(1+(-0.0126+0.01475*sar_h)) as float)  , '#,###,##0.00')  as esp_h, 
 (SELECT TOP 1 texcl FROM chtexturegrp AS chtg INNER JOIN chtexture AS cht ON chtg.chtgkey=cht.chtgkey  AND chtg.rvindicator = 'yes' AND chtg.chkey=cha.chkey) AS tcl,
major_mu_pct_sum, mu_pct_sum
FROM (#M4 AS MA44 INNER JOIN (component AS coa INNER JOIN  chorizon   AS cha  ON cha.cokey=coa.cokey AND cha.hzdept_r < 15 ) ON MA44.cokey=coa.cokey AND MA44.majcompflag = 'Yes' );

• Calculates ESP from SAR.
• Adjusts OM and clay to stay within property ranges of the prediction equation.

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.400000006	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.200000003	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.400000006	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.100000001	1	2	-1.28	-1.28	0.21
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.100000001	1	2	-1.28	-1.28	0.21
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.200000003	1	20	28	38	0	1	2	-1.28	-0.97	0.21
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.200000003	1	14	24	30	0	1	2	-1.28	-0.97	0.21
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.100000001	1	2	-1.28	-1.28	0.21
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.100000001	1	2	-1.28	-1.28	0.21
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.400000006	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.100000001	2	-1.28	-1.28	-1.28
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21

Agg2 Table

CREATE TABLE #agg2
(  aoiid INT ,
landunit CHAR(20), 
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag  CHAR(3),
localphase CHAR(60),
 hzname CHAR(20),
 hzdept_r INT,
 hzdepb_r INT,
claytotall FLOAT,
claytotalr FLOAT,
claytotalh FLOAT,
oml  FLOAT,
omr FLOAT,
omh  FLOAT,
 sar_l FLOAT,
 sar_r FLOAT,
 sar_h FLOAT,
 cec7_l FLOAT,
 cec7_r FLOAT,
 cec7_h FLOAT,
 ec_l FLOAT,
 ec_r FLOAT,
 ec_h FLOAT,
 esp_l FLOAT,
esp_r FLOAT,
esp_h FLOAT, 
tcl CHAR(40),
sandy INT,  major_mu_pct_sum INT,
 mu_pct_sum INT);

INSERT INTO #agg2
SELECT DISTINCT
aoiid ,
landunit, 
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r,
claytotall,
claytotalr,
claytotalh,
oml ,
omr ,
omh ,
sar_l,
sar_r,
sar_h,
cec7_l,
cec7_r,
cec7_h,
ec_l,
ec_r,
ec_h,
CASE WHEN cec7_l < 50 + 0 and (cec7_l) IS NOT NULL  and (sar_l) IS NOT NULL and sar_l !=0 and sar_l < 40 + 0  and ec_l < 20 + 0 then esp_l
            WHEN  sar_l !=0 and  (sar_l) IS NOT NULL then 1.5*sar_l/(1 + 0.015*sar_l)
            WHEN sar_l < 0.01 then 0 else null END AS esp_l, 

 CASE WHEN cec7_r < 50 + 0 and (cec7_r) IS NOT NULL  and (sar_r) IS NOT NULL and sar_r !=0 and sar_r < 40 + 0  and ec_r < 20 + 0 then esp_r
            WHEN  sar_r !=0 and  (sar_r) IS NOT NULL then 1.5*sar_r/(1 + 0.015*sar_r)
            WHEN sar_r < 0.01 then 0 else null END AS esp_r, 

CASE WHEN cec7_h < 50 + 0 and (cec7_h) IS NOT NULL  and (sar_h) IS NOT NULL and sar_h !=0 and sar_h < 40 + 0  and ec_h < 20 + 0 then esp_h
            WHEN  sar_h !=0 and  (sar_h) IS NOT NULL then 1.5*sar_h/(1 + 0.015*sar_h)
           WHEN sar_h < 0.01 then 0 else null END AS esp_h, 
tcl, 
CASE WHEN  tcl ='Loamy coarse sand' THEN 1
WHEN  tcl = 'Loamy fine sand' THEN 1
WHEN  tcl = 'Loamy sand'  THEN 1
WHEN  tcl = 'Sand' THEN 1
WHEN  tcl = 'Coarse sand' THEN 1
WHEN  tcl = 'Fine sand' THEN 1 ELSE 0 END AS sandy, major_mu_pct_sum, mu_pct_sum
FROM #agg1;

• Determine if the texture is sandy.
• Adjusts OM and clay to stay within property ranges of the prediction equation.

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg3 Table

CREATE TABLE #agg3
(  aoiid INT ,
landunit CHAR(20), 
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag  CHAR(3),
localphase CHAR(60),
 hzname CHAR(20),
 hzdept_r INT,
 hzdepb_r INT,
claytotall FLOAT,
claytotalr FLOAT,
claytotalh FLOAT,
oml FLOAT,
omr FLOAT,
omh FLOAT,
sandy INT, 
AgStab_l FLOAT,
AgStab_r FLOAT,
AgStab_h FLOAT,
tcl CHAR(40),   major_mu_pct_sum INT, mu_pct_sum INT)

INSERT INTO #agg3
SELECT DISTINCT 
aoiid ,
landunit, 
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r, 
claytotall,
claytotalr,
claytotalh,
oml,
omr,
omh,
sandy,
FORMAT (49.7+13.7*LOG(oml) + 0.61*claytotall-0.0045*POWER(claytotall,2) - 0.28*esp_h-0.06*POWER(esp_h,2), '#,###,##0.00')  AS AgStab_l,
FORMAT (49.7+13.7*LOG(omr) + 0.61*claytotalr-0.0045*POWER(claytotalr,2) - 0.28*esp_r-0.06*POWER(esp_r,2), '#,###,##0.00')  AS AgStab_r,
FORMAT (49.7+13.7*LOG(omh) + 0.61*claytotalh-0.0045*POWER(claytotalh,2) - 0.28*esp_l-0.06*POWER(esp_l,2), '#,###,##0.00')  AS AgStab_h, 
tcl,  major_mu_pct_sum ,  mu_pct_sum 
FROM #agg2;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg4 Table

CREATE TABLE #agg4
(  aoiid INT ,
landunit CHAR(20), 
landunit_acres FLOAT, 
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag  CHAR(3),
localphase CHAR(60),
 hzname CHAR(20),
 hzdept_r INT,
 hzdepb_r INT,
AgStab_l FLOAT,
AgStab_r FLOAT,
AgStab_h FLOAT,
tcl CHAR(40),  
major_mu_pct_sum INT, mu_pct_sum INT,
adj_comp_pct FLOAT, 
thickness INT,
AGG_InRangeTop_0_15 INT,
AGG_InRangeBot_0_15 INT
)
;

INSERT INTO #agg4
SELECT DISTINCT ag.aoiid ,
ag.landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r, 
CASE WHEN AgStab_l > 100  THEN 100 WHEN claytotall >= 0  and claytotall < 5 THEN null WHEN sandy=1 THEN null WHEN oml > 20 THEN null ELSE AgStab_l END AS AgStab_l,
CASE WHEN AgStab_r > 100  THEN 100 WHEN claytotalr >= 0  and claytotalr < 5 THEN null WHEN sandy=1 THEN null WHEN omr > 20 THEN null ELSE AgStab_r END AS AgStab_r,
CASE WHEN AgStab_h > 100  THEN 100 WHEN claytotalh >= 0  and claytotalh < 5 THEN null WHEN sandy=1 THEN null WHEN omh > 20 THEN null ELSE AgStab_h END AS AgStab_h,
tcl, major_mu_pct_sum, mu_pct_sum, (1.0 * comppct_r / major_mu_pct_sum) AS adj_comp_pct, CASE WHEN hzdepb_r IS NULL THEN 0
WHEN hzdept_r IS NULL THEN 0 ELSE hzdepb_r-hzdept_r END AS thickness, 
CASE  WHEN hzdept_r < 15 then hzdept_r ELSE 0 END AS AGG_InRangeTop_0_15, 
CASE  WHEN hzdepb_r <= 15 THEN hzdepb_r WHEN hzdepb_r > 15 and hzdept_r < 15 THEN 15 ELSE 0 END AS AGG_InRangeBot_0_15
FROM #AoiAcres
LEFT OUTER JOIN #agg3 AS ag ON ag.aoiid=#AoiAcres.aoiid WHERE majcompflag = 'yes' GROUP BY ag.aoiid ,
ag.landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r, AgStab_l , AgStab_h, AgStab_r, claytotall, claytotalr, claytotalh, sandy,comppct_r , major_mu_pct_sum ,mu_pct_sum , oml, omr, omh, tcl;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg5 Table

CREATE TABLE #agg5
(  aoiid INT ,
landunit CHAR(20), 
landunit_acres FLOAT, 
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag  CHAR(3),
localphase CHAR(60),
 hzname CHAR(20),
 hzdept_r INT,
 hzdepb_r INT,
AgStab_l FLOAT,
AgStab_r FLOAT,
AgStab_h FLOAT,
tcl CHAR(40),  
major_mu_pct_sum INT,mu_pct_sum INT,
adj_comp_pct FLOAT, 
thickness INT,
AGG_InRangeTop_0_15 INT,
AGG_InRangeBot_0_15 INT,
InRangeThickness INT, 
InRangeSumThickness INT )
;

INSERT INTO #agg5
SELECT DISTINCT  aoiid ,
landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r, 
AgStab_l,
AgStab_r,
AgStab_h,
tcl, 
major_mu_pct_sum, mu_pct_sum,
adj_comp_pct,
thickness, 
AGG_InRangeTop_0_15, 
AGG_InRangeBot_0_15,
CASE WHEN AGG_InRangeTop_0_15 IS NULL THEN 0 
WHEN AGG_InRangeBot_0_15 IS NULL THEN 0 ELSE AGG_InRangeBot_0_15 - AGG_InRangeTop_0_15 END AS InRangeThickness,
SUM (CASE WHEN AGG_InRangeTop_0_15 IS NULL THEN 0 
WHEN AGG_InRangeBot_0_15 IS NULL THEN 0 ELSE AGG_InRangeBot_0_15 - AGG_InRangeTop_0_15 END) over(PARTITION BY cokey, aoiid) AS InRangeSumThickness
FROM #agg4
GROUP BY aoiid ,
landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
comppct_r,
majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r, 
AgStab_l,
AgStab_r,
AgStab_h,
tcl, major_mu_pct_sum,
mu_pct_sum,
adj_comp_pct,
thickness, 
AGG_InRangeTop_0_15, 
AGG_InRangeBot_0_15 ;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg6 Table

CREATE TABLE #agg6
( aoiid INT,
landunit CHAR(20),  
landunit_acres FLOAT,
mukey INT,
mapunit_acres FLOAT, 
cokey INT,
compname CHAR(60),
localphase CHAR(60),
major_mu_pct_sum INT,mu_pct_sum INT,
adj_comp_pct FLOAT ,
--AGG_InRangeTop_0_15 INT, 
--AGG_InRangeBot_0_15 INT, 
--InRangeThickness INT, 
--InRangeSumThickness INT, 
--AgStab_l FLOAT,
--AgStab_r FLOAT, 
--AgStab_h FLOAT,
comp_weighted_average_l FLOAT, 
comp_weighted_average_r FLOAT, 
comp_weighted_average_h FLOAT
)
;

INSERT INTO #agg6
SELECT DISTINCT  aoiid ,
landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
localphase, major_mu_pct_sum,
mu_pct_sum,
adj_comp_pct,
--AGG_InRangeTop_0_15, 
--AGG_InRangeBot_0_15,
--InRangeThickness,
--InRangeSumThickness, 
--AgStab_l ,
--AgStab_r , 
--AgStab_h ,
SUM ((CAST (InRangeThickness AS FLOAT)/CAST (InRangeSumThickness AS FLOAT)) * AgStab_l)  over(PARTITION BY ag5.cokey, aoiid) AS comp_weighted_average_l,
SUM((CAST (InRangeThickness AS FLOAT)/CAST (InRangeSumThickness AS FLOAT)) * AgStab_r)  over(PARTITION BY ag5.cokey, aoiid) AS comp_weighted_average_r,
SUM((CAST (InRangeThickness AS FLOAT)/CAST (InRangeSumThickness AS FLOAT)) * AgStab_h)  over(PARTITION BY ag5.cokey, aoiid) comp_weighted_average_h
FROM  #agg5 AS ag5 
WHERE InRangeSumThickness !=0
GROUP BY aoiid, landunit, 
landunit_acres,
mukey,
mapunit_acres, 
cokey,
compname,
localphase, major_mu_pct_sum ,
mu_pct_sum,
adj_comp_pct,-- AgStab_l ,
--AgStab_r , 
--AgStab_h ,
--AGG_InRangeTop_0_15, 
--AGG_InRangeBot_0_15,
InRangeThickness,
InRangeSumThickness, AgStab_l, AgStab_r, AgStab_h ;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Map Unit Aggregation for Mapunit Table

CREATE TABLE #agg7
( aoiid INT,
landunit CHAR(20),  
landunit_acres FLOAT,
mukey INT,
mapunit_acres FLOAT, 
major_mu_pct_sum INT,mu_pct_sum INT,
MU_SUM_AGG_L FLOAT, 
MU_SUM_AGG_R FLOAT, 
MU_SUM_AGG_H FLOAT
--MU_Weighted_Average_R FLOAT
)
;

-- Map Unit Aggregation
INSERT INTO #agg7
SELECT DISTINCT aoiid ,
landunit, 
landunit_acres,
mukey,
mapunit_acres, 
major_mu_pct_sum, mu_pct_sum,
 FORMAT ( SUM (adj_comp_pct * comp_weighted_average_l) over(PARTITION BY ag6.mukey, aoiid )  , '#,###,##0.00') AS MU_SUM_AGG_L,
FORMAT (SUM (adj_comp_pct * comp_weighted_average_r) over(PARTITION BY ag6.mukey, aoiid )  , '#,###,##0.00') AS MU_SUM_AGG_R,
FORMAT (SUM (adj_comp_pct * comp_weighted_average_h) over(PARTITION BY ag6.mukey, aoiid )  , '#,###,##0.00') ASMU_SUM_AGG_H
--(mapunit_acres/landunit_acres)*MU_SUM_AGG_R AS MU_Weighted_Average_R

FROM #agg6 AS ag6
GROUP BY aoiid ,
landunit, 
landunit_acres,
mukey,
mapunit_acres, 
major_mu_pct_sum , mu_pct_sum,
adj_comp_pct,
comp_weighted_average_l,
comp_weighted_average_r,
comp_weighted_average_h 
;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg7a Table

CREATE TABLE #agg7a
( aoiid INT,
landunit CHAR(20),  
landunit_acres FLOAT,
mapunit_acres FLOAT,
MU_SUM_AGG_L  FLOAT, 
MU_SUM_AGG_R FLOAT, 
MU_SUM_AGG_H FLOAT
)
;

INSERT INTO #agg7a
SELECT DISTINCT 
 aoiid ,
 landunit,  
 landunit_acres,
 mapunit_acres,
CASE WHEN MU_SUM_AGG_R = 0 THEN 0 ELSE  MU_SUM_AGG_L END AS MU_SUM_AGG_L , 
MU_SUM_AGG_R , 
CASE WHEN MU_SUM_AGG_R = 0 THEN 0 ELSE  MU_SUM_AGG_H END AS MU_SUM_AGG_H
FROM #agg7
GROUP BY aoiid, landunit, mapunit_acres, landunit_acres, MU_SUM_AGG_L, MU_SUM_AGG_R, MU_SUM_AGG_H;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Agg8 Table

CREATE TABLE #agg8
( aoiid INT,
landunit CHAR(20),  
landunit_acres FLOAT,
LU_AGG_Weighted_Average_L FLOAT, 
LU_AGG_Weighted_Average_R FLOAT, 
LU_AGG_Weighted_Average_H FLOAT
)
;

INSERT INTO #agg8
SELECT DISTINCT 
 aoiid ,
 landunit,  
 landunit_acres,
 FORMAT (SUM ((mapunit_acres/landunit_acres)*MU_SUM_AGG_L) over(partition by aoiid)  , '#,###,##0.00') AS LU_AGG_Weighted_Average_L, 
 FORMAT (SUM ((mapunit_acres/landunit_acres)*MU_SUM_AGG_R) over(partition by aoiid)  , '#,###,##0.00') AS LU_AGG_Weighted_Average_R,
 FORMAT (SUM ((mapunit_acres/landunit_acres)*MU_SUM_AGG_H) over(partition by aoiid)  , '#,###,##0.00') AS LU_AGG_Weighted_Average_H
FROM #agg7a
GROUP BY aoiid, landunit, mapunit_acres, landunit_acres, MU_SUM_AGG_L, MU_SUM_AGG_R, MU_SUM_AGG_H;

aoiid	landunit	mukey	mapunit_acres	cokey	compname	comppct_r	majcompflag	localphase	hzname	hzdept_r	hzdepb_r	claytotall	claytotalr	claytotalh	oml	omr	omh	sar_l	sar_r	sar_h	cec7_l	cec7_r	cec7_h	ec_l	ec_r	ec_h	esp_l	esp_r	esp_h	tcl	major_mu_pct_sum	mu_pct_sum
1	T9981Fld3	354627	0.426	16464494	Daglum	25	Yes	NULL	H1	0	18	18	22	26	2	3	4	0	3	5	10	15	20	0	0	0	-1.28	3.07	5.76	Loam	90	100
1	T9981Fld3	354627	0.426	16464495	Farnuf	65	Yes	NULL	H1	0	20	20	23.5	27	2	3	4	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	90	100
1	T9981Fld3	354648	0.287	16464607	Amor	25	Yes	NULL	H1	0	20	15	20	25	3	4.5	6	0	0	0	15	17.5	20	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H1	0	13	10	18.5	27	3	4	5	0	1	1	20	25	30	0	0	0	-1.28	0.21	0.21	Loam	85	100
1	T9981Fld3	354648	0.287	16464612	Reeder	60	Yes	NULL	H2	13	48	18	28	35	1	2	3	0	3	5	15	22.5	30	0	0	0	-1.28	3.07	5.76	Clayloam	85	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Ap	0	13	18	22	27	2	3	4	0	0	0	15	19	23	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663930	Amor	49	Yes	NULL	Bw1	13	23	18	22	30	1	2	3	0	0	0	15	19	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Ap	0	13	14	19	27	1	2	3	0	0	0	12	15	19	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2494708	1.729	16663931	Cabba	32	Yes	NULL	Bk	13	38	15	22	35	0.5	0.8	1	0	0	0	8	15	25	0	1	2	-1.28	-1.28	-1.28	Loam	81	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
1	T9981Fld3	2525720	56.699	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
1	T9981Fld3	2525720	56.699	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
1	T9981Fld3	2525732	1.35	16663796	Ekalaka	55	Yes	NULL	Ap	0	15	10	14	18	1	1.5	2	0	1	2	8	13	18	0	1	2	-1.28	0.21	1.66	Finesandyloam	72	100
1	T9981Fld3	2525732	1.35	16663797	Yegen	17	Yes	NULL	Ap	0	15	10	15	20	2	3	4	0	0	0	10	17	24	0	0	0	-1.28	-1.28	-1.28	Finesandyloam	72	100
1	T9981Fld3	2525733	0.129	16663951	Vebar	50	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525733	0.129	16663952	Cohagen	25	Yes	NULL	Ap	0	15	10	14	18	0.5	1	2	0	0	0	9	12	15	0	1	2	-1.28	-1.28	-1.28	Finesandyloam	75	100
1	T9981Fld3	2525739	28.479	16663915	Parshall	20	Yes	NULL	Ap	0	15	10	14	18	2	3.5	4	0	0	0	9	13	16	0	0.2	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525739	28.479	16663917	Vebar	58	Yes	NULL	Ap	0	15	10	14	18	1.5	2	2.5	0	0	0	9	12	16	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	78	100
1	T9981Fld3	2525745	4.983	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
1	T9981Fld3	2525746	16.106	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
1	T9981Fld3	2525754	12.638	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
1	T9981Fld3	2525764	17.691	16663611	Regan	55	Yes	saline,occasionallyflooded	Az	0	23	18	25	27	2	4	6	0	0	0	15	23	29	5	8	16	-1.28	-1.28	-1.28	Siltloam	55	100
1	T9981Fld3	2525769	181.356	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
1	T9981Fld3	2525769	181.356	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
1	T9981Fld3	2755648	2.449	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
1	T9981Fld3	2755648	2.449	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100
1	T9981Fld3	2755654	4.599	16663846	Reeder	60	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
1	T9981Fld3	2755654	4.599	16663847	Amor	25	Yes	NULL	Ap	0	20	15	20	25	2	3	4	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	85	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	A	0	7	18	23	27	4	5.5	7	0	0	1	18	27	36	0	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	Btn	12	46	30	38	50	1.5	2	3	5	10	15	21	30	44	0	1	2	5.76	11.89	17.26	Siltyclayloam	88	100
2	T9981Fld4	2525720	8.623	16663899	Daglum	33	Yes	NULL	E	7	12	18	20	27	2.5	3.5	4.5	0	1	2	15	21	31	0	0.5	1	-1.28	0.21	1.66	Siltloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	Btn	8	20	35	38	50	0.5	1.25	2	5	12	20	22	28	42	0	2	4	5.76	14.12	22.02	Clayloam	88	100
2	T9981Fld4	2525720	8.623	16663903	Rhoades	55	Yes	NULL	E	0	8	18	24	27	3	4	5	0	4	10	16	25	32	0	1	2	-1.28	4.43	11.89	Loam	88	100
2	T9981Fld4	2525724	0.458	16664017	Savage	30	Yes	NULL	Ap	0	15	27	32	38	2	3	4	0	0.2	1	20	28	38	0	1	2	-1.28	-0.97	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664018	Daglum	20	Yes	NULL	Ap	0	15	27	32	40	2	3	4	0	0	1	20	28	40	0	1	2	-1.28	-1.28	0.21	Clayloam	85	100
2	T9981Fld4	2525724	0.458	16664022	Belfield	35	Yes	NULL	Ap	0	15	18	25	27	2	3	4	0	0.2	1	14	24	30	0	1	2	-1.28	-0.97	0.21	Siltloam	85	100
2	T9981Fld4	2525730	31.514	16663991	Regent	68	Yes	NULL	Ap	0	18	27	34	40	2	3	4	0	0	0	21	27	32	0	0.5	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525730	31.514	16663992	Savage	17	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	0	21	26	32	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	85	100
2	T9981Fld4	2525745	62.205	16663921	Shambo	75	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0.5	1	14	22	32	0	0.5	2	-1.28	-0.53	0.21	Loam	75	100
2	T9981Fld4	2525746	63.55	16663927	Shambo	78	Yes	NULL	Ap	0	15	18	22	27	2	3.5	5	0	0	0	15	22	32	0	0.5	2	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	Btn	5	46	35	37	50	1	2	3	13	19	25	23	30	46	4	10	16	15.19	21.11	26.26	Clayloam	75	100
2	T9981Fld4	2525754	23.138	16663602	Harriet	75	Yes	occasionallyflooded	E	0	5	12	22	27	3	4.5	6	0	0	0	13	24	34	0	1	2	-1.28	-1.28	-1.28	Loam	75	100
2	T9981Fld4	2525769	103.909	16663985	Belfield	48	Yes	NULL	Ap	0	18	18	23	27	2	4	6	0	0	1	14	24	34	0.1	1	2	-1.28	-1.28	0.21	Siltloam	88	100
2	T9981Fld4	2525769	103.909	16663987	Daglum	40	Yes	NULL	Ap	0	18	27	32	40	2	3	4	0	0	1	20	28	40	0.1	1	2	-1.28	-1.28	0.21	Clayloam	88	100
2	T9981Fld4	2755639	0.443	16663554	Savage	62	Yes	NULL	Ap	0	18	27	33	40	1	2	3	0	0	0	18	27	38	0	0	0	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	A	13	25	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755639	0.443	16663555	Grail	18	Yes	NULL	Ap	0	13	27	31	35	4	5	6	0	0	0	24	32	40	0	1	2	-1.28	-1.28	-1.28	Siltyclayloam	80	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	A	0	13	8	7	10	0.5	1.5	2.25	0	0	0	3	8	12	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663957	Flasher	30	Yes	NULL	AC	13	25	8	5	10	0.25	1	1.5	0	0	0	1	6	10	0	1	2	-1.28	-1.28	-1.28	Loamyfinesand	88	100
2	T9981Fld4	2755643	9.641	16663958	Vebar	40	Yes	NULL	A	0	15	10	14	18	1.5	2	3	0	0	0	9	15	20	0	0.4	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755643	9.641	16663959	Tally	18	Yes	NULL	A	0	15	10	14	18	1.5	2.5	3.5	0	0	0	9	13	16	0	0.1	2	-1.28	-1.28	-1.28	Finesandyloam	88	100
2	T9981Fld4	2755648	11.382	16663766	Reeder	58	Yes	NULL	Ap	0	20	18	23	27	1	2	3	0	0	0	13	20	28	0	0	0	-1.28	-1.28	-1.28	Loam	78	100
2	T9981Fld4	2755648	11.382	16663767	Janesburg	20	Yes	NULL	Ap	0	20	18	22	27	2	3	4	0	0	1	15	21	30	0	0	1	-1.28	-1.28	0.21	Siltloam	78	100

Final Landunit Rating

SELECT DISTINCT  landunit, landunit_acres,
CASE WHEN LU_AGG_Weighted_Average_R <25 THEN 'Low'
WHEN LU_AGG_Weighted_Average_R >=25 AND  LU_AGG_Weighted_Average_R <50 THEN 'Moderate'
WHEN LU_AGG_Weighted_Average_R >=50 AND  LU_AGG_Weighted_Average_R <75 THEN 'Moderately High' 
WHEN LU_AGG_Weighted_Average_R >=75 THEN 'High'
WHEN LU_AGG_Weighted_Average_R IS NULL THEN 'Not Rated'
END AS rating_class,
CASE WHEN LU_AGG_Weighted_Average_R <25 THEN CONCAT ('Aggregate Stability', ':', 1)
WHEN LU_AGG_Weighted_Average_R >=25 AND  LU_AGG_Weighted_Average_R <50 THEN CONCAT ('Aggregate Stability', ':', 2)
WHEN LU_AGG_Weighted_Average_R >=50 AND  LU_AGG_Weighted_Average_R <75 THEN CONCAT ('Aggregate Stability', ':', 3)
WHEN LU_AGG_Weighted_Average_R >=75 THEN CONCAT ('Aggregate Stability', ':', 4)
WHEN LU_AGG_Weighted_Average_R IS NULL THEN CONCAT ('Aggregate Stability', ':', 'Not Rated')
END AS rating_key,
 'Aggregate Stability' AS attributename,
LU_AGG_Weighted_Average_L AS [Aggregate_Stability_L],
LU_AGG_Weighted_Average_R AS [Aggregate_Stability_R],
LU_AGG_Weighted_Average_H AS [Aggregate_Stability_H]
FROM #agg8

landunit|landunit_acres|rating_class|rating_key|attributename|Aggregate_Stability_L|Aggregate_Stability_R|Aggregate_Stability_H T9981 Fld3 |328.952|Moderately High|Aggregate Stability:3|Aggregate Stability|54.45|64.07|70.55 T9981 Fld4 |318.722|Moderately High|Aggregate Stability:3|Aggregate Stability|53.11|61.41|67.4

References

1. Blanco-Canqui, H., and R. Lal. 2004. Mechanisms of carbon sequestration in soil aggregates. Criti. Rev. Plant Sci. 23:481–504. doi: 10.1080/07352680490886842

2. Cambardella, C.A., and E.T. Elliott. 1993. Carbon and nitrogen distribution in aggregates from cultivated and native grassland soils. Soil Sci. Soc. Am. J. 57:1071–1076. doi: 10.2136/sssaj1993.03615995005700040032x

3. Denef, K., J. Six, H. Bossuyt, S.D. Frey, E.T. Elliott, R. Merckx, and K. Paustian. 2001. Influence of dry-wet cycles on the interrelationship between aggregate, particulate organic matter, and microbial community dynamics. Soil Biol. Biochem. 33:1599–1611. doi: 10.1016/s0038-0717(01)00076-1

4. Gale, W.J., and C.A. Cambardella. 2000. Carbon dynamics of surface residue- and root-derived organic matter under simulated no-till. Soil Sci. Soc. Am. J. 64:190–195. doi: 10.2136/sssaj2000.641190x

5. Gale, W.J., C.A. Cambardella, and T.B. Bailey. 2000a. Root-derived carbon and the formation and stabilization of aggregates. Soil Sci. Soc. Am. J. 64:201–207. doi: 10.2136/sssaj2000.641201x

6. Gale, W.J., C.A. Cambardella, and T.B. Bailey. 2000b. Surface residue- and root-derived carbon in stable and unstable aggregates. Soil Sci. Soc. Am. J. 64:196–201. doi: 10.2136/sssaj2000.641196x

7. Martin, J.P. 1971. Decomposition and binding action of polysaccharides in soil. Soil Biol. Biochem. 3:33–41.

8. Six, J., E.T. Elliott, and K. Paustian. 1999. Aggregate and soil organic matter dynamics under conventional and no-tillage systems. Soil Sci. Soc. Am. J. 63:1350–1358.

9. Six, J., K. Paustian, E.T. Elliott, and C. Combrink. 2000. Soil structure and organic matter: I. Distribution of aggregate-size classes and aggregate-associated carbon. Soil Sci. Soc. Am. J. 64:681–689.

10. Tisdall, J.M., and J.M. Oades. 1982. Organic matter and water-stable aggregates in soil. J. Soil Sci. 33:141–163.

11. USDA-ARS. 1966. Aggregate stability of soils from western United States and Canada. Tech. Bull. No. 1355. Agricultural Research Service, United States Department of Agriculture in cooperation with Colorado Agricultural Experiment Station. U.S. Government Printing Office. Washington, D.C.