SELECT l.[labsampnum],[result_source_key],[prep_code] ,cnn.[pedon_key],cnn.[site_key],p.[pedlabsampnum],[project_key] ,[layer_field_label_1],[hzn_top],[hzn_bot], CASE WHEN [hzn_top] < 15 then [hzn_top] ELSE 0 END AS InRangeTop_0_15 ,CASE WHEN [hzn_bot] <= 15 THEN [hzn_bot] WHEN [hzn_bot] > 15 and [hzn_top] < 15 THEN 15 ELSE 0 END AS InRangeBot_0_15,[hzn_desgn],[pedoniid],[upedonid] ,CASE WHEN [corr_name] IS NULL THEN [samp_name] ELSE [corr_name] END AS soil_name,CASE WHEN [corr_class_type] IS NULL THEN [samp_class_type] ELSE [corr_class_type] END AS class_type ,CASE WHEN [corr_classification_name] IS NULL THEN [samp_classification_name] ELSE [corr_classification_name] END AS [classification_name] ,[siteiid],[usiteid],[site_obsdate],[latitude_decimal_degrees],[longitude_decimal_degrees],country_code ,country_name , state_code , state_name , county_code , county_name , mlra_code , mlra_name , ssa_code , ssa_name ,[note],[pedobjupdate]
,CASE WHEN [estimated_organic_carbon] IS NOT NULL THEN LEFT (ROUND ([estimated_organic_carbon]* 1.724, 2),4)
WHEN [estimated_organic_carbon] IS NULL AND [caco3_lt_2_mm] IS NOT NULL THEN LEFT (ROUND (([total_carbon_ncs]- ([caco3_lt_2_mm]*0.12))* 1.724, 2),4)
WHEN [estimated_organic_carbon] IS NULL AND [caco3_lt_2_mm] IS NULL THEN LEFT (ROUND ([total_carbon_ncs]* 1.724,2),4) END AS [estimated_organic_matter], (SELECT TOP 1 [texture_lab]
FROM [lab_physical_properties] AS lpp WHERE lpp.labsampnum=l.labsampnum AND [texture_lab] IS NOT NULL) AS [lab_texture]
,[ca_nh4_ph_7],[ca_nh4_ph_7_method],[mg_nh4_ph_7],[mg_nh4_ph_7_method],[na_nh4_ph_7],[na_nh4_ph_7_method],[k_nh4_ph_7],[k_nh4_ph_7_method],[acidity_bacl2_tea_ph_8_2],[acidity_bacl2_tea_ph_82_method],[aluminum_kcl_extractable],[aluminum_kcl_extract_method],[manganese_kcl_extractable],[manganese_kcl_extract_method],[iron_kcl_extractable],[iron_kcl_extractable_method],[cec_nh4_ph_7],[cec_nh4_ph_7_method],[total_carbon_ncs],[total_carbon_ncs_method],[total_nitrogen_ncs],[total_nitrogen_ncs_method],[total_sulfur_ncs],[total_sulfur_ncs_method],[organic_carbon_walkley_black],[oc_walkley_black_method],[fe_dithionite_citrate_extract],[iron_dc_extract_method],[aluminum_dithionite_citrate],[aluminum_dc_extract_method],[manganese_dithionite_citrate],[manganese_dc_extract_method],[ammoniumoxalate_opticaldensity],[ammonium_ox_opt_dens_method],[fe_ammoniumoxalate_extractable],[iron_ammonium_oxalate_method],[aluminum_ammonium_oxalate],[al_ammonium_oxalate_method],[silica_ammonium_oxalate],[silica_ammonium_oxalate_method],[manganese_ammonium_oxalate],[mn_ammonium_oxalate_method],[carbon_sodium_pyro_phospate],[c_na_pyro_phosphate_method],[iron_sodium_pyro_phosphate],[iron_na_pyro_phosphate_method],[aluminum_na_pyro_phosphate],[aluminum_na_pyro_phosphate_met],[manganese_na_pyro_phosphate],[mn_na_pyro_phosphate_method],[ph_kcl],[ph_kcl_method],[ph_cacl2],[ph_cacl2_method],[ph_h2o],[ph_h2o_method],[ph_saturated_paste],[ph_saturated_paste_method],[ph_oxidized],[ph_oxidized_initial],[ph_oxidized_method],[ph_naf],[ph_naf_method],[ph_water_extract],[ph_water_extract_method],[caco3_lt_2_mm],[caco3_lt_2_mm_method],[corrected_gypsum_lt_2_mm],[corrected_gyp_lt_2_mm_method],[resistivity_saturated_paste],[resistivity_sp_method],[ca_satx],[ca_satx_method],[mg_satx],[mg_satx_method],[ca_plus_mg_satx],[ca_plus_mg_satx_method],[na_satx],[na_satx_method],[k_satx],[k_satx_method],[co3_satx],[co3_satx_method],[hco3_satx],[hco3_satx_method],[co3_plus_hco3_satx],[co3_plus_hco3_satx_method],[cl_satx],[cl_satx_method],[f_satx],[f_satx_method],[po4_satx],[po4_satx_method],[br_satx],[br_satx_method],[oac_satx],[oac_satx_method],[so4_satx],[so4_satx_method],[no2_satx],[no2_satx_method],[no3_satx],[no3_satx_method],[h20_satx],[h20_satx_method],[electrical_conductivity_satx],[electrical_cond_satx_method],[ec_predict_one_to_two],[ec_predict_one_to_two_method],[melanic_index],[melanic_index_method],[new_zealand_phosphorus_retent],[new_zealand_phos_retent_method],[phosphorus_ammonium_oxalate],[phosphorus_ammonium_oxalate_method],[phosphorus_anion_resin_one_hr],[phosphorus_anion_resin_24_hr],[phosphorus_anion_resin_method],[phosphorus_bray1],[phosphorus_bray1_method],[phosphorus_bray2],[phosphorus_bray2_method],[phosphorus_citric_acid],[phosphorus_citric_acid_method],[phosphorus_mehlich_3],[phosphorus_mehlich_3_method],[phosphorus_olsen],[phosphorus_olsen_method],[phosphorus_water],[phosphorus_water_method],[nitrate_1m_kcl],[nitrate_1m_kcl_method],[water_extract_method],[acetate_water_extractable],[aluminum_water_extractable],[arsenic_water_extractable],[barium_water_extractable],[boron_water_extractable],[bromide_water_extractable],[cadmium_water_extractable],[calcium_water_extractable],[chloride_water_extractable],[chromium_water_extractabe],[cobalt_water_extractable],[copper_water_extracable],[ec_water_extract],[fluoride_water_extractable],[iron_water_extractable],[lead_water_extractable],[magnesium_water_extractable],[manganese_water_extractable],[molybdenum_water_extractable],[nickel_water_extractable],[nitrate_n_water_extractable],[nitrate_water_extractable],[nitrite_water_extractable],[phosphorus_water_extractable],[phosphate_water_extractable],[potassium_water_extractable],[selenium_water_extractable],[silicon_water_extractable],[sodium_water_extractable],[strontium_water_extractable],[sulfate_water_extractable],[vanadium_water_extractable],[zinc_water_extractable],[mehlich_3_extractable_method],[aluminum_mehlich3_extractable],[arsenic_mehlich3_extractable],[barium_mehlich3_extractable],[cadmium_mehlich3_extractable],[calcium_mehlich3_extractable],[chromium_mehlich3_extractable],[cobalt_mehlich3_extractable],[copper_mehlich3_extractable],[iron_mehlich3_extractable],[lead_mehlich3_extractable],[magnesium_mehlich3_extractable],[manganese_mehlich3_extractable],[molybdenum_mehlich3_extractabl],[nickel_mehlich3_extractable],[phosphorus_mehlich3_extractable],[potassium_mehlich3_extractable],[selenium_mehlich3_extractable],[silicon_mehlich3_extractable],[sodium_mehlich3_extractable],[strontium_mehlich3_extractable],[zinc_mehlich3_extractable],[sum_of_nh4_ph_7_Ext_bases],[sum_of_cations_cec_pH_8_2],[ecec_base_plus_aluminum],[aluminum_saturation],[base_sat_sum_of_cations_ph_8_2],[base_sat_nh4oac_ph_7],[estimated_organic_carbon],[carbon_to_nitrogen_ratio],[aluminum_plus_half_iron_oxalat],[caco3_lt_20_mm],[gypsum_lt_20_mm],[ca_to_mg_ratio],[total_estimated_salts_satx],[exchangeable_sodium],[sodium_absorption_ratio],[phosphorus_anion_resin_capacit]
FROM [lab_combine_nasis_ncss] AS cnn
INNER JOIN [lab_pedon] AS p ON p.pedon_key=cnn.pedon_key
INNER JOIN [lab_layer] AS l ON l.pedon_key=p.pedon_key AND ([hzn_top] <= 15 AND [hzn_desgn] NOT LIKE '%O%')
INNER JOIN [lab_chemical_properties] AS lcp ON lcp.labsampnum=l.labsampnum AND [total_carbon_ncs] IS NOT NULL
INNER JOIN (SELECT [area_code] AS country_code,[area_name] AS country_name, area_key FROM lab_area
) AS c ON cnn.[country_key]=c.area_key
INNER JOIN (SELECT [area_code] AS state_code,[area_name] AS state_name, area_key FROM lab_area
WHERE [area_code] = 'WI')
AS s ON cnn.[state_key]=s.area_key
INNER JOIN (SELECT [area_code] AS county_code,[area_name] AS county_name, area_key FROM lab_area
) AS ct ON cnn.[county_key]=ct.area_key
INNER JOIN (SELECT [area_code] AS mlra_code,[area_name] AS mlra_name, area_key FROM lab_area
) AS m ON cnn.[mlra_key]=m.area_key
INNER JOIN (SELECT [area_code] AS ssa_code,[area_name] AS ssa_name, area_key FROM lab_area
) AS ss ON cnn.[ssa_key]=ss.area_key