Examples of Query Parts

Use 'AS' For Aliases

Change area symbol header in the output table to be more readable.

areasymbol AS Area_Symbol

Clip the Left Side of a Value

Clips the left two digits of the area symbol and labels it as State.

LEFT(areasymbol,2) AS State

Clip the Right Side of a Value

Clips the right three characters of the area symbol and labels it as County.

RIGHT(areasymbol, 3) AS County

Round a Value.

Calculates the percentage map unit acres for the area and rounds the value to two digits. Make sure to cast back to a float or it becomes a text value.

ROUND((CAST(muacres*100 as float)/areaovacres),2) AS Map_unit_percentage

Convert Values and Concatenate Two Values Together

Converts the slope value to three places and concatenates the low and high slope values into one value and labels it Slope.

CONVERT(varchar(3), slope_l) + ' to ' + CONVERT(varchar(3),slope_h) AS Slope

CASE WHEN ELSE Statement

If the sub class Land category class is empty then just use the main class otherwise concatenate the class and sub class together and label it Non_irrigated_land_class.

CASE when component.nirrcapscl is null

THEN component.nirrcapcl

ELSE component.nirrcapcl + component.nirrcapscl

END AS Non_irrigated_land_class

Select the First Value in an Array of Values

Select the first restriction kind when more than one value is in the table.

(SELECT TOP 1 reskind

FROM component

LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey

WHERE component.cokey = c.cokey AND reskind IS NOT NULL) AS Restriction_kind

Select Condition for an Interpretation That Has the Greatest Value

Sum the Component percentages for the NCCPI values and select the first value in a descending list.

(SELECT TOP 1 interphrc

FROM mapunit AS m2

INNER JOIN component AS c2 ON c2.mukey=m2.mukey

INNER JOIN cointerp AS ci2 ON c2.cokey = ci2.cokey AND m2.mukey = mu.mukey AND ruledepth = 0 AND mrulename LIKE 'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'

GROUP BY interphrc, comppct_r

ORDER BY SUM(comppct_r) over(PARTITION BY interphrc) DESC) AS Dominant_component_class

Sum Values with "partition by and Over" Clause

Sum the percentage map unit acres in an area and label it as Total_acres.

SELECT SUM(mo.areaovacres*comppct_r/100) over (partition by compname,lo.areasymbol) as Total_acres

Rank Values with "partition by and Over" Clause

Place a ranking value on each area based on the total acres.

RANK() over (partition by areasymbol order by total_acres desc) as Ranked_area

Place Data into a Temporary Table to be used in a Secondary Query

Places all data in the select clause into a Temporary table named #T1.

INTO #T1

Drop Tables if an INTO Statement is Used

Drop Table #T1.

DROP TABLE #T1

List Interpretation Reasons

List the array of interpretation reasons into one field.

interphrc + ': ' + ISNULL(restrictions, '') AS Interpretation_class

SUBSTRING( ( SELECT ( '; ' + interphrc)

WHERE t1.cokey = t2.cokey

ORDER BY t1.cokey, t2.cokey

FOR XML PATH('') ), 3, 1000) AS Restrictions

Select Dominant Component

Be sure to join this subquery back to the main query.

c.cokey =

(SELECT TOP 1 c.cokey

FROM component AS c

INNER JOIN mapunit AS mu ON c.mukey=mu.mukey and mu.mukey=mapunit.mukey

ORDER BY c.comppct_r DESC)

Select Dominant Condition

Be sure to join this subquery back to the main query.

(SELECT TOP 1 interphrc

FROM mapunit AS m

INNER JOIN component AS c ON c.mukey=m.mukey

INNER JOIN cointerp AS ci ON c.cokey = ci.cokey AND m.mukey = mapunit.mukey AND ruledepth = 0 AND mrulename LIKE 'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'

GROUP BY interphrc, comppct_r

ORDER BY SUM(comppct_r) over(PARTITION BY interphrc) DESC) AS Dominant_class,