“Drainage class (natural)” refers to the frequency and duration of wet periods under conditions similar to those under which the soil formed. Alterations of the water regime by human activities, either through drainage or irrigation, are not a consideration unless they have significantly changed the morphology of the soil. Seven classes of natural soil drainage are recognized: excessively drained, somewhat excessively drained, well drained, moderately well drained, somewhat poorly drained, poorly drained, and very poorly drained. These classes are defined in the “Soil Survey Manual.”

Script Breakdown

Insert identifier(s) string and WKT geometry for each area of interest (AOI) polygon

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);
aoiid landunit aoigeom
1 T9981 Fld3 POLYGON ((-102.13386921506947 45.944643788188387, -102.12327175652177 45.9447036058142, -102.12335160658608 45.959173206572416, -102.13402890980223 45.959218442561564, -102.13386921506947 45.944643788188387))
2 T9981 Fld4 POLYGON ((-102.12327175652177 45.9447036058142, -102.1128892282776 45.944710506326032, -102.1130336443976 45.959162795100383, -102.12335160658608 45.959173206572416, -102.12327175652177 45.9447036058142))

Create summary scres 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;

Populate soil map unit acres, aggregated by mukey (merges polygons together)

INSERT INTO #M2
SELECT DISTINCT M1.aoiid, M1.landunit, M1.mukey,
ROUND (SUM (M1.poly_acres) OVER(PARTITION BY M1.landunit, M1.mukey), 3) AS mapunit_acres
FROM #AoiSoils2 AS M1
GROUP BY M1.aoiid, M1.landunit, M1.mukey, M1.poly_acres;

Component level data and mapunit sum-of-comppct_r (major components only)

CREATE TABLE #M4
(   aoiid INT,
    landunit CHAR(20),
    mukey INT,
    mapunit_acres FLOAT,
    cokey INT,
    compname CHAR(60),
    comppct_r INT,
    majcompflag CHAR(3),
    mu_pct_sum INT, 
    major_mu_pct_sum INT, 
    drainagecl CHAR(254)
    );
---Populate component level data with cokey, comppct_r and mapunit sum-of-comppct_r
INSERT INTO #M4
SELECT M2.aoiid, M2.landunit, M2.mukey, mapunit_acres, CO.cokey, CO.compname, CO.comppct_r, CO.majcompflag, (SELECT SUM (CCO.comppct_r) 
FROM #M2 AS MM2
INNER JOIN component AS CCO ON CCO.mukey=MM2.mukey  AND M2.mukey=MM2.mukey AND majcompflag = 'Yes'  )  AS  major_mu_pct_sum,
SUM (CO.comppct_r) OVER(PARTITION BY M2.landunit, M2.mukey) AS mu_pct_sum, drainagecl
FROM #M2 AS M2
INNER JOIN component AS CO ON CO.mukey = M2.mukey
aoiid landunit mukey mapunit_acres cokey compname comppct_r majcompflag mu_pct_sum major_mu_pct_sum drainagecl
1 T9981 Fld3 354627 0.426 16464493 Slickspots 2 No 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464494 Daglum 25 Yes 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464495 Farnuf 65 Yes 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464496 Grail 3 No 90 100 Moderately well drained
1 T9981 Fld3 354627 0.426 16464497 Rhoades 3 No 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464498 Tally 2 No 90 100 Well drained
1 T9981 Fld3 354648 0.287 16464607 Amor 25 Yes 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464608 Arnegard 4 No 85 100 Moderately well drained
1 T9981 Fld3 354648 0.287 16464609 Belfield 4 No 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464610 Heil 1 No 85 100 Poorly drained
1 T9981 Fld3 354648 0.287 16464611 Lantry 3 No 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464612 Reeder 60 Yes 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464613 Vebar 3 No 85 100 Well drained
1 T9981 Fld3 2494708 1.729 16663928 Regent 5 No 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663929 Chama 5 No 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663930 Amor 49 Yes 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663931 Cabba 32 Yes 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663932 Shambo 9 No 81 100 Well drained
1 T9981 Fld3 2525720 56.699 16663899 Daglum 33 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663900 Savage 3 No 176 100 Well drained
1 T9981 Fld3 2525720 56.699 16663901 Barkof 2 No 176 100 Well drained
1 T9981 Fld3 2525720 56.699 16663902 Rhoades 2 No 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663903 Rhoades 55 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663904 Belfield 5 No 176 100 Moderately well drained
1 T9981 Fld3 2525732 1.35 16663795 Lakota 4 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663796 Ekalaka 55 Yes 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663797 Yegen 17 Yes 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663798 Desart 14 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663799 Parshall 6 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663800 Rhoades 2 No 72 100 Moderately well drained
1 T9981 Fld3 2525732 1.35 16663801 Vebar 2 No 72 100 Well drained
1 T9981 Fld3 2525733 0.129 16663950 Beisigl 7 No 75 100 Somewhat excessively drained
1 T9981 Fld3 2525733 0.129 16663951 Vebar 50 Yes 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663952 Cohagen 25 Yes 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663953 Tally 14 No 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663954 Amor 2 No 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663955 Arnegard 2 No 75 100 Well drained
1 T9981 Fld3 2525739 28.479 16663915 Parshall 20 Yes 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663916 Tally 12 No 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663917 Vebar 58 Yes 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663918 Arnegard 8 No 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663919 Cohagen 2 No 78 100 Well drained
1 T9981 Fld3 2525745 4.983 16663920 Farnuf 12 No 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663921 Shambo 75 Yes 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663922 Arnegard 10 No 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663923 Amor 3 No 150 100 Well drained
1 T9981 Fld3 2525746 16.106 16663924 Arnegard 10 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663925 Farnuf 8 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663926 Amor 4 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663927 Shambo 78 Yes 156 100 Well drained
1 T9981 Fld3 2525754 12.638 16663598 Heil 3 No 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663599 Rhoades 4 No 150 100 Moderately well drained
1 T9981 Fld3 2525754 12.638 16663600 Daglum 2 No 150 100 Moderately well drained
1 T9981 Fld3 2525754 12.638 16663601 Vanda 5 No 150 100 Well drained
1 T9981 Fld3 2525754 12.638 16663602 Harriet 75 Yes 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663603 Regan 6 No 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663604 Glenross 5 No 150 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663605 Peta 2 No 55 100 Somewhat poorly drained
1 T9981 Fld3 2525764 17.691 16663606 Dimmick 6 No 55 100 Very poorly drained
1 T9981 Fld3 2525764 17.691 16663607 Arveson 12 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663608 Regan 10 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663609 Harriet 7 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663610 Straw 3 No 55 100 Well drained
1 T9981 Fld3 2525764 17.691 16663611 Regan 55 Yes 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663612 Marysland 5 No 55 100 Poorly drained
1 T9981 Fld3 2525766 0.032 16663539 Water 100 Yes 100 100 NULL
1 T9981 Fld3 2525769 181.356 16663985 Belfield 48 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663986 Grail 5 No 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663987 Daglum 40 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663988 Savage 5 No 176 100 Well drained
1 T9981 Fld3 2525769 181.356 16663989 Rhoades 2 No 176 100 Moderately well drained
1 T9981 Fld3 2755648 2.449 16663766 Reeder 58 Yes 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663767 Janesburg 20 Yes 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663768 Amor 10 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663769 Dogtooth 5 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663770 Regent 3 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663771 Belfield 2 No 156 100 Moderately well drained
1 T9981 Fld3 2755648 2.449 16663772 Barkof 2 No 156 100 Well drained
1 T9981 Fld3 2755654 4.599 16663846 Reeder 60 Yes 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663847 Amor 25 Yes 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663848 Belfield 4 No 85 100 Moderately well drained
1 T9981 Fld3 2755654 4.599 16663849 Regent 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663850 Vebar 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663851 Chama 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663852 Arnegard 2 No 85 100 Well drained
2 T9981 Fld4 2525720 8.623 16663899 Daglum 33 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663900 Savage 3 No 176 100 Well drained
2 T9981 Fld4 2525720 8.623 16663901 Barkof 2 No 176 100 Well drained
2 T9981 Fld4 2525720 8.623 16663902 Rhoades 2 No 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663903 Rhoades 55 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663904 Belfield 5 No 176 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664017 Savage 30 Yes 85 100 Well drained
2 T9981 Fld4 2525724 0.458 16664018 Daglum 20 Yes 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664019 Grail 8 No 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664020 Regent 5 No 85 100 Well drained
2 T9981 Fld4 2525724 0.458 16664021 Rhoades 2 No 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664022 Belfield 35 Yes 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663990 Daglum 2 No 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663991 Regent 68 Yes 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663992 Savage 17 Yes 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663993 Cabba 2 No 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663994 Grail 6 No 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663995 Moreau 5 No 85 100 Well drained
2 T9981 Fld4 2525745 62.205 16663920 Farnuf 12 No 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663921 Shambo 75 Yes 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663922 Arnegard 10 No 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663923 Amor 3 No 150 100 Well drained
2 T9981 Fld4 2525746 63.55 16663924 Arnegard 10 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663925 Farnuf 8 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663926 Amor 4 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663927 Shambo 78 Yes 156 100 Well drained
2 T9981 Fld4 2525754 23.138 16663598 Heil 3 No 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663599 Rhoades 4 No 150 100 Moderately well drained
2 T9981 Fld4 2525754 23.138 16663600 Daglum 2 No 150 100 Moderately well drained
2 T9981 Fld4 2525754 23.138 16663601 Vanda 5 No 150 100 Well drained
2 T9981 Fld4 2525754 23.138 16663602 Harriet 75 Yes 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663603 Regan 6 No 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663604 Glenross 5 No 150 100 Poorly drained
2 T9981 Fld4 2525767 3.86 16663540 Water 100 Yes 100 100 NULL
2 T9981 Fld4 2525769 103.909 16663985 Belfield 48 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663986 Grail 5 No 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663987 Daglum 40 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663988 Savage 5 No 176 100 Well drained
2 T9981 Fld4 2525769 103.909 16663989 Rhoades 2 No 176 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663552 Regent 3 No 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663553 Lawther 2 No 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663554 Savage 62 Yes 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663555 Grail 18 Yes 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663556 Belfield 8 No 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663557 Daglum 2 No 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663558 Farland 5 No 80 100 Well drained
2 T9981 Fld4 2755643 9.641 16663956 Telfer 3 No 88 100 Somewhat excessively drained
2 T9981 Fld4 2755643 9.641 16663957 Flasher 30 Yes 88 100 Somewhat excessively drained
2 T9981 Fld4 2755643 9.641 16663958 Vebar 40 Yes 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663959 Tally 18 Yes 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663960 Parshall 5 No 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663961 Amor 4 No 88 100 Well drained
2 T9981 Fld4 2755648 11.382 16663766 Reeder 58 Yes 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663767 Janesburg 20 Yes 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663768 Amor 10 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663769 Dogtooth 5 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663770 Regent 3 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663771 Belfield 2 No 156 100 Moderately well drained
2 T9981 Fld4 2755648 11.382 16663772 Barkof 2 No 156 100 Well drained

Adjusting Component Percent

CREATE TABLE #drain
(  aoiid INT ,
landunit CHAR(20), 
landunit_acres FLOAT, 
mukey INT,
mapunit_acres FLOAT,
cokey INT, 
compname CHAR(280),
comppct_r INT, 
majcompflag CHAR(4), 
mu_pct_sum INT, 
major_mu_pct_sum INT, 

drainagecl CHAR(40), 
adj_comp_pct FLOAT
)
;
INSERT INTO #drain
SELECT #M4.aoiid, #M4.landunit, #AoiAcres.landunit_acres,  mukey, mapunit_acres, cokey, compname, comppct_r, majcompflag, mu_pct_sum, major_mu_pct_sum , drainagecl, FORMAT ((1.0 * comppct_r / major_mu_pct_sum), '#,###,##0.00')  AS adj_comp_pct 
FROM #M4 
LEFT OUTER JOIN #AoiAcres ON #AoiAcres.aoiid=#M4.aoiid WHERE majcompflag = 'Yes' ;
aoiid landunit landunit_acres mukey mapunit_acres cokey compname comppct_r majcompflag mu_pct_sum major_mu_pct_sum drainagecl adj_comp_pct
1 T9981 Fld3 328.952 354627 0.426 16464494 Daglum 25 Yes 100 90 Well drained 0.28
1 T9981 Fld3 328.952 354627 0.426 16464495 Farnuf 65 Yes 100 90 Well drained 0.72
1 T9981 Fld3 328.952 354648 0.287 16464607 Amor 25 Yes 100 85 Well drained 0.29
1 T9981 Fld3 328.952 354648 0.287 16464612 Reeder 60 Yes 100 85 Well drained 0.71
1 T9981 Fld3 328.952 2494708 1.729 16663930 Amor 49 Yes 100 81 Well drained 0.6
1 T9981 Fld3 328.952 2494708 1.729 16663931 Cabba 32 Yes 100 81 Well drained 0.4
1 T9981 Fld3 328.952 2525720 56.699 16663899 Daglum 33 Yes 100 88 Moderately well drained 0.38
1 T9981 Fld3 328.952 2525720 56.699 16663903 Rhoades 55 Yes 100 88 Moderately well drained 0.63
1 T9981 Fld3 328.952 2525732 1.35 16663796 Ekalaka 55 Yes 100 72 Well drained 0.76
1 T9981 Fld3 328.952 2525732 1.35 16663797 Yegen 17 Yes 100 72 Well drained 0.24
1 T9981 Fld3 328.952 2525733 0.129 16663951 Vebar 50 Yes 100 75 Well drained 0.67
1 T9981 Fld3 328.952 2525733 0.129 16663952 Cohagen 25 Yes 100 75 Well drained 0.33
1 T9981 Fld3 328.952 2525739 28.479 16663915 Parshall 20 Yes 100 78 Well drained 0.26
1 T9981 Fld3 328.952 2525739 28.479 16663917 Vebar 58 Yes 100 78 Well drained 0.74
1 T9981 Fld3 328.952 2525745 4.983 16663921 Shambo 75 Yes 100 75 Well drained 1
1 T9981 Fld3 328.952 2525746 16.106 16663927 Shambo 78 Yes 100 78 Well drained 1
1 T9981 Fld3 328.952 2525754 12.638 16663602 Harriet 75 Yes 100 75 Poorly drained 1
1 T9981 Fld3 328.952 2525764 17.691 16663611 Regan 55 Yes 100 55 Poorly drained 1
1 T9981 Fld3 328.952 2525766 0.032 16663539 Water 100 Yes 100 100 NULL 1
1 T9981 Fld3 328.952 2525769 181.356 16663985 Belfield 48 Yes 100 88 Moderately well drained 0.55
1 T9981 Fld3 328.952 2525769 181.356 16663987 Daglum 40 Yes 100 88 Moderately well drained 0.45
1 T9981 Fld3 328.952 2755648 2.449 16663766 Reeder 58 Yes 100 78 Well drained 0.74
1 T9981 Fld3 328.952 2755648 2.449 16663767 Janesburg 20 Yes 100 78 Well drained 0.26
1 T9981 Fld3 328.952 2755654 4.599 16663846 Reeder 60 Yes 100 85 Well drained 0.71
1 T9981 Fld3 328.952 2755654 4.599 16663847 Amor 25 Yes 100 85 Well drained 0.29
2 T9981 Fld4 318.722 2525720 8.623 16663899 Daglum 33 Yes 100 88 Moderately well drained 0.38
2 T9981 Fld4 318.722 2525720 8.623 16663903 Rhoades 55 Yes 100 88 Moderately well drained 0.63
2 T9981 Fld4 318.722 2525724 0.458 16664017 Savage 30 Yes 100 85 Well drained 0.35
2 T9981 Fld4 318.722 2525724 0.458 16664018 Daglum 20 Yes 100 85 Moderately well drained 0.24
2 T9981 Fld4 318.722 2525724 0.458 16664022 Belfield 35 Yes 100 85 Moderately well drained 0.41
2 T9981 Fld4 318.722 2525730 31.514 16663991 Regent 68 Yes 100 85 Well drained 0.8
2 T9981 Fld4 318.722 2525730 31.514 16663992 Savage 17 Yes 100 85 Well drained 0.2
2 T9981 Fld4 318.722 2525745 62.205 16663921 Shambo 75 Yes 100 75 Well drained 1
2 T9981 Fld4 318.722 2525746 63.55 16663927 Shambo 78 Yes 100 78 Well drained 1
2 T9981 Fld4 318.722 2525754 23.138 16663602 Harriet 75 Yes 100 75 Poorly drained 1
2 T9981 Fld4 318.722 2525767 3.86 16663540 Water 100 Yes 100 100 NULL 1
2 T9981 Fld4 318.722 2525769 103.909 16663985 Belfield 48 Yes 100 88 Moderately well drained 0.55
2 T9981 Fld4 318.722 2525769 103.909 16663987 Daglum 40 Yes 100 88 Moderately well drained 0.45
2 T9981 Fld4 318.722 2755639 0.443 16663554 Savage 62 Yes 100 80 Well drained 0.78
2 T9981 Fld4 318.722 2755639 0.443 16663555 Grail 18 Yes 100 80 Moderately well drained 0.23
2 T9981 Fld4 318.722 2755643 9.641 16663957 Flasher 30 Yes 100 88 Somewhat excessively drained 0.34
2 T9981 Fld4 318.722 2755643 9.641 16663958 Vebar 40 Yes 100 88 Well drained 0.45
2 T9981 Fld4 318.722 2755643 9.641 16663959 Tally 18 Yes 100 88 Well drained 0.2
2 T9981 Fld4 318.722 2755648 11.382 16663766 Reeder 58 Yes 100 78 Well drained 0.74
2 T9981 Fld4 318.722 2755648 11.382 16663767 Janesburg 20 Yes 100 78 Well drained 0.26

Calculating component acres

CREATE TABLE #drain2
(  aoiid INT ,
landunit CHAR(20), 
landunit_acres FLOAT, 
mukey INT,
mapunit_acres FLOAT,
cokey INT, 
compname CHAR(280),
comppct_r INT, 
majcompflag CHAR(4), 
mu_pct_sum INT, 
drainagecl CHAR(40), 
adj_comp_pct FLOAT, 
co_acres FLOAT
)
;

INSERT INTO #drain2
SELECT aoiid, landunit, landunit_acres,  mukey, mapunit_acres, cokey, compname, comppct_r, majcompflag, mu_pct_sum, drainagecl, adj_comp_pct, ROUND ( (adj_comp_pct * mapunit_acres), 2) AS co_acres
FROM #drain;
aoiid landunit landunit_acres mukey mapunit_acres cokey compname comppct_r majcompflag mu_pct_sum drainagecl adj_comp_pct co_acres
1 T9981 Fld3 328.952 354627 0.426 16464494 Daglum 25 Yes 100 Well drained 0.28 0.12
1 T9981 Fld3 328.952 354627 0.426 16464495 Farnuf 65 Yes 100 Well drained 0.72 0.31
1 T9981 Fld3 328.952 354648 0.287 16464607 Amor 25 Yes 100 Well drained 0.29 0.08
1 T9981 Fld3 328.952 354648 0.287 16464612 Reeder 60 Yes 100 Well drained 0.71 0.2
1 T9981 Fld3 328.952 2494708 1.729 16663930 Amor 49 Yes 100 Well drained 0.6 1.04
1 T9981 Fld3 328.952 2494708 1.729 16663931 Cabba 32 Yes 100 Well drained 0.4 0.69
1 T9981 Fld3 328.952 2525720 56.699 16663899 Daglum 33 Yes 100 Moderately well drained 0.38 21.55
1 T9981 Fld3 328.952 2525720 56.699 16663903 Rhoades 55 Yes 100 Moderately well drained 0.63 35.72
1 T9981 Fld3 328.952 2525732 1.35 16663796 Ekalaka 55 Yes 100 Well drained 0.76 1.03
1 T9981 Fld3 328.952 2525732 1.35 16663797 Yegen 17 Yes 100 Well drained 0.24 0.32
1 T9981 Fld3 328.952 2525733 0.129 16663951 Vebar 50 Yes 100 Well drained 0.67 0.09
1 T9981 Fld3 328.952 2525733 0.129 16663952 Cohagen 25 Yes 100 Well drained 0.33 0.04
1 T9981 Fld3 328.952 2525739 28.479 16663915 Parshall 20 Yes 100 Well drained 0.26 7.4
1 T9981 Fld3 328.952 2525739 28.479 16663917 Vebar 58 Yes 100 Well drained 0.74 21.07
1 T9981 Fld3 328.952 2525745 4.983 16663921 Shambo 75 Yes 100 Well drained 1 4.98
1 T9981 Fld3 328.952 2525746 16.106 16663927 Shambo 78 Yes 100 Well drained 1 16.11
1 T9981 Fld3 328.952 2525754 12.638 16663602 Harriet 75 Yes 100 Poorly drained 1 12.64
1 T9981 Fld3 328.952 2525764 17.691 16663611 Regan 55 Yes 100 Poorly drained 1 17.69
1 T9981 Fld3 328.952 2525766 0.032 16663539 Water 100 Yes 100 NULL 1 0.03
1 T9981 Fld3 328.952 2525769 181.356 16663985 Belfield 48 Yes 100 Moderately well drained 0.55 99.75
1 T9981 Fld3 328.952 2525769 181.356 16663987 Daglum 40 Yes 100 Moderately well drained 0.45 81.61
1 T9981 Fld3 328.952 2755648 2.449 16663766 Reeder 58 Yes 100 Well drained 0.74 1.81
1 T9981 Fld3 328.952 2755648 2.449 16663767 Janesburg 20 Yes 100 Well drained 0.26 0.64
1 T9981 Fld3 328.952 2755654 4.599 16663846 Reeder 60 Yes 100 Well drained 0.71 3.27
1 T9981 Fld3 328.952 2755654 4.599 16663847 Amor 25 Yes 100 Well drained 0.29 1.33
2 T9981 Fld4 318.722 2525720 8.623 16663899 Daglum 33 Yes 100 Moderately well drained 0.38 3.28
2 T9981 Fld4 318.722 2525720 8.623 16663903 Rhoades 55 Yes 100 Moderately well drained 0.63 5.43
2 T9981 Fld4 318.722 2525724 0.458 16664017 Savage 30 Yes 100 Well drained 0.35 0.16
2 T9981 Fld4 318.722 2525724 0.458 16664018 Daglum 20 Yes 100 Moderately well drained 0.24 0.11
2 T9981 Fld4 318.722 2525724 0.458 16664022 Belfield 35 Yes 100 Moderately well drained 0.41 0.19
2 T9981 Fld4 318.722 2525730 31.514 16663991 Regent 68 Yes 100 Well drained 0.8 25.21
2 T9981 Fld4 318.722 2525730 31.514 16663992 Savage 17 Yes 100 Well drained 0.2 6.3
2 T9981 Fld4 318.722 2525745 62.205 16663921 Shambo 75 Yes 100 Well drained 1 62.2
2 T9981 Fld4 318.722 2525746 63.55 16663927 Shambo 78 Yes 100 Well drained 1 63.55
2 T9981 Fld4 318.722 2525754 23.138 16663602 Harriet 75 Yes 100 Poorly drained 1 23.14
2 T9981 Fld4 318.722 2525767 3.86 16663540 Water 100 Yes 100 NULL 1 3.86
2 T9981 Fld4 318.722 2525769 103.909 16663985 Belfield 48 Yes 100 Moderately well drained 0.55 57.15
2 T9981 Fld4 318.722 2525769 103.909 16663987 Daglum 40 Yes 100 Moderately well drained 0.45 46.76
2 T9981 Fld4 318.722 2755639 0.443 16663554 Savage 62 Yes 100 Well drained 0.78 0.35
2 T9981 Fld4 318.722 2755639 0.443 16663555 Grail 18 Yes 100 Moderately well drained 0.23 0.1
2 T9981 Fld4 318.722 2755643 9.641 16663957 Flasher 30 Yes 100 Somewhat excessively drained 0.34 3.28
2 T9981 Fld4 318.722 2755643 9.641 16663958 Vebar 40 Yes 100 Well drained 0.45 4.34
2 T9981 Fld4 318.722 2755643 9.641 16663959 Tally 18 Yes 100 Well drained 0.2 1.93
2 T9981 Fld4 318.722 2755648 11.382 16663766 Reeder 58 Yes 100 Well drained 0.74 8.42
2 T9981 Fld4 318.722 2755648 11.382 16663767 Janesburg 20 Yes 100 Well drained 0.26 2.96

Summary by landunit

SELECT DISTINCT aoiid, landunit, landunit_acres, drainagecl , ROUND (SUM (co_acres) over(partition by aoiid, drainagecl),2)  AS drainage_class_acres,

 CASE WHEN drainagecl = 'Excessively drained' THEN CONCAT ('Drainage Class', ':', 1) 
 WHEN drainagecl = 'Somewhat excessively drained' THEN CONCAT ('Drainage Class', ':', 2) 
 WHEN drainagecl = 'Well drained' THEN CONCAT ('Drainage Class', ':', 3) 
 WHEN drainagecl = 'Moderately well drained' THEN CONCAT ('Drainage Class', ':', 4) 
 WHEN drainagecl = 'Somewhat poorly drained' THEN CONCAT ('Drainage Class', ':', 5) 
 WHEN drainagecl = 'Poorly drained' THEN CONCAT ('Drainage Class', ':', 6) 
 WHEN drainagecl = 'Very poorly drained' THEN CONCAT ('Drainage Class', ':', 7) 
 WHEN drainagecl = 'Subaqueous' THEN CONCAT ('Drainage Class', ':', 8) 
 WHEN drainagecl IS NULL  THEN CONCAT ('Drainage Class', ':', 9)    END      
  AS rating_key ,
  'Drainage class' AS attributename
FROM #drain2

ORDER BY aoiid, drainage_class_acres DESC
;
aoiid landunit landunit_acres drainagecl drainage_class_acres rating_key attributename
1 T9981 Fld3 328.952 Moderately well drained 238.63 Drainage Class:4 Drainage class
1 T9981 Fld3 328.952 Well drained 60.53 Drainage Class:3 Drainage class
1 T9981 Fld3 328.952 Poorly drained 30.33 Drainage Class:6 Drainage class
1 T9981 Fld3 328.952 NULL 0.03 Drainage Class:9 Drainage class
2 T9981 Fld4 318.722 Well drained 175.42 Drainage Class:3 Drainage class
2 T9981 Fld4 318.722 Moderately well drained 113.02 Drainage Class:4 Drainage class
2 T9981 Fld4 318.722 Poorly drained 23.14 Drainage Class:6 Drainage class
2 T9981 Fld4 318.722 NULL 3.86 Drainage Class:9 Drainage class
2 T9981 Fld4 318.722 Somewhat excessively drained 3.28 Drainage Class:2 Drainage class