Export EQuIS Data Queries

The following queries are used by EnviroInsite when exporting EQuIS data into an Access database (Reports> Generate Access Database). Each section represents the code for creating that table of the database.

1. Locations

SELECT Location,Interval,Easting,
                         Northing,
                         [Surface Elevation],
                         [Well Bottom Depth],
                         CLASS,
                         [Top of Casing],
                         Azimuth,
                         Incline,
                         [Top Depth],
                         [Bottom Depth],
                         Grp
FROM
  (SELECT l.sys_loc_code AS LocationID,
          l.sys_loc_code AS Location,
          COALESCE(s.s_screen, '-') AS Interval,
          COALESCE(lt.location_type_desc, l.loc_desc, l.loc_type, '-') AS CLASS,
          CAST(dbo.fn_unit_conversion(COALESCE (l.total_depth, w.depth_of_well, 0), COALESCE (l.units, w.depth_unit, f.elev_unit), COALESCE(f.elev_unit, l.units, w.depth_unit), NULL) AS float) AS [Well Bottom Depth],
          COALESCE (l.total_depth,
                    w.depth_of_well,
                    0) AS total_depth,
                   CASE
                       WHEN dbo.fn_to_number(c.elev) IS NULL
                            AND @bUseDefaultElev = 0 THEN NULL
                       ELSE CAST(COALESCE(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev), COALESCE (c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float)
                   END AS [Surface Elevation],
                   CAST(COALESCE(dbo.fn_unit_conversion(COALESCE(w.top_casing_elev, dbo.fn_to_number(c.elev)), COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, w.depth_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float) AS [Top of Casing],
                   COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,
                   COALESCE(dbo.fn_to_number(l.plunge), -90) + 90 AS Incline,
                   CAST(dbo.fn_unit_conversion(COALESCE(ws_start_depth, 0), COALESCE (ws_depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, ws_depth_unit, c.elev_unit, l.units), NULL) AS float) AS [Top Depth],
                   CAST(dbo.fn_unit_conversion(COALESCE(ws_end_depth, 0), COALESCE (ws_depth_unit, c.elev_unit, l.units, f.elev_unit),COALESCE(f.elev_unit, ws_depth_unit, c.elev_unit, l.units), NULL) AS float) AS [Bottom Depth],
                   COALESCE(gu.geologic_unit_desc, '-') AS Grp,
                   depth AS [Depth To Water],
                   c.Easting,
                   c.Northing,
                   Colocated
   FROM
     (SELECT facility_id,
             loc.status_flag,
             sys_loc_code,
             loc_name,
             loc_desc,
             loc_type,
             total_depth,
             units,
             bearing,
             plunge
      FROM dt_location loc
      WHERE facility_id = @id
        AND (loc.status_flag = 'A'
             OR loc.status_flag IS NULL)) l
   INNER JOIN dt_facility f ON l.facility_id = f.facility_id
   INNER JOIN
     (SELECT CAST(x_coord AS float) AS Easting,
             CAST(y_coord AS float) AS Northing,
             dt_coordinate.facility_id,
             sys_loc_code,
             dt_coordinate.coord_type_code,
             dt_coordinate.identifier,
             observation_date,
             x_coord,
             y_coord,
             elev,
             dt_coordinate.elev_unit,
             coord_zone,
             elev_datum_code
      FROM dt_coordinate
      INNER JOIN dt_facility ON dt_coordinate.facility_id = dt_facility.facility_id
      AND dt_coordinate.coord_type_code = dt_facility.coord_type_code
      AND dt_facility.identifier = dt_coordinate.identifier
      WHERE dt_facility.facility_id = @id
        AND (dbo.fn_to_number(x_coord) IS NOT NULL)
        AND (dbo.fn_to_number(y_coord) IS NOT NULL)
        AND COALESCE (x_coord,
                      y_coord) IS NOT NULL
        AND CAST(x_coord AS float) >= @dXMin
        AND CAST(x_coord AS float) <= @dXMax
        AND CAST(y_coord AS float) >= @dYMin
        AND CAST(y_coord AS float) <= @dYMax ) c ON c.facility_id = l.facility_id
   AND l.sys_loc_code = c.sys_loc_code
   AND c.coord_type_code = f.coord_type_code
   AND c.identifier = f.identifier
   LEFT OUTER JOIN dt_well w ON l.facility_id = w.facility_id
   AND l.sys_loc_code = w.sys_loc_code
   LEFT OUTER JOIN
     (SELECT l2.sys_loc_code slc,
             STUFF(
                     (SELECT ', ' + l1.sys_loc_code
                      FROM dt_location l1
                      WHERE l1.parent_loc_code = l2.sys_loc_code
                        AND l1.facility_id = @ID
                        FOR XML PATH(''), TYPE) .value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') Colocated
      FROM dt_location l2
      WHERE l2.facility_id = @ID
      GROUP BY l2.sys_loc_code) cl ON l.sys_loc_code = cl.slc
   LEFT OUTER JOIN
     (SELECT sys_loc_code,
             facility_id,
             AVG(depth) AS depth
      FROM dt_water_table
      GROUP BY sys_loc_code,
               facility_id) wt ON l.facility_id = wt.facility_id
   AND l.sys_loc_code = wt.sys_loc_code
   LEFT OUTER JOIN rt_geologic_unit gu ON w.geologic_unit_code = gu.geologic_unit_code
   LEFT OUTER JOIN rt_location_type lt ON lt.location_type_code = l.loc_type
   LEFT OUTER JOIN
     (SELECT ws.facility_id,
             ws.sys_loc_code,
             ws.start_depth AS ws_start_depth,
             ws.end_depth AS ws_end_depth,
             ws.depth_unit AS ws_depth_unit,
             COALESCE('SCRN-'+LTRIM(STR(ws.start_depth, 12, 2))+'-'+LTRIM(STR(ws.end_depth, 12, 2)), '-') AS s_screen
      FROM dt_well_segment ws
      WHERE segment_type IN (@segment_type0)
      UNION SELECT DISTINCT smpl.facility_id,
                            smpl.sys_loc_code,
                            smpl.start_depth,
                            smpl.end_depth,
                            smpl.depth_unit,
                            COALESCE(smpl.matrix_code + '_' +LTRIM(STR(smpl.start_depth, 12, 2))+'-'+LTRIM(STR(smpl.end_depth, 12, 2)), '-') AS s_screen
      FROM dt_sample smpl
      WHERE start_depth IS NOT NULL
        AND smpl.matrix_code NOT IN (@mx0)) s ON s.facility_id = l.facility_id
   AND s.sys_loc_code = l.sys_loc_code) inner_table
WHERE COALESCE(Location, '') LIKE @sWell
  AND Interval LIKE @sScreen
  AND COALESCE(CLASS, '') LIKE @sClass
ORDER BY Location,
         [Top Depth] ASC

"INSERT INTO Locations (Location, Longitude, Latitude, [Surface Elevation], [Well Bottom Depth], [Class], [Top of Casing], Azimuth, Incline, Notes) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"

/////////////////////////////////////////////////

2. Intervals

"SELECT LOCATION,Interval,Easting,
Northing,
[Surface Elevation],
[Well Bottom Depth],
CLASS,
[Top of Casing],
Azimuth,
Incline,
[Top Depth],
[Bottom Depth],
Grp
FROM
(SELECT l.sys_loc_code AS LocationID,
l.sys_loc_code AS LOCATION,
COALESCE(s.s_screen, '-') AS Interval,
COALESCE(lt.location_type_desc, l.loc_desc, l.loc_type, '-') AS CLASS,
CAST(dbo.fn_unit_conversion(COALESCE (l.total_depth, w.depth_of_well, 0), COALESCE (l.units, w.depth_unit, f.elev_unit), COALESCE(f.elev_unit, l.units, w.depth_unit), NULL) AS float) AS [Well Bottom Depth],
COALESCE (l.total_depth,
w.depth_of_well,
0) AS total_depth,
CASE
WHEN dbo.fn_to_number(c.elev) IS NULL
AND @bUseDefaultElev = 0 THEN NULL
ELSE CAST(COALESCE(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev), COALESCE (c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float)
END AS [Surface Elevation],
CAST(COALESCE(dbo.fn_unit_conversion(COALESCE(w.top_casing_elev, dbo.fn_to_number(c.elev)), COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, w.depth_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float) AS [Top of Casing],
COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,
COALESCE(dbo.fn_to_number(l.plunge), -90) + 90 AS Incline,
CAST(dbo.fn_unit_conversion(COALESCE(ws_start_depth, 0), COALESCE (ws_depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, ws_depth_unit, c.elev_unit, l.units), NULL) AS float) AS [Top Depth],
CAST(dbo.fn_unit_conversion(COALESCE(ws_end_depth, 0), COALESCE (ws_depth_unit, c.elev_unit, l.units, f.elev_unit),COALESCE(f.elev_unit, ws_depth_unit, c.elev_unit, l.units), NULL) AS float) AS [Bottom Depth],
COALESCE(gu.geologic_unit_desc, '-') AS Grp,
depth AS [Depth To Water],
c.Easting,
c.Northing,
Colocated
FROM
(SELECT facility_id,
loc.status_flag,
sys_loc_code,
loc_name,
loc_desc,
loc_type,
total_depth,
units,
bearing,
plunge
FROM dt_location loc
WHERE facility_id = @id
AND (loc.status_flag = 'A'
OR loc.status_flag IS NULL)) l
INNER JOIN dt_facility f ON l.facility_id = f.facility_id
INNER JOIN
(SELECT CAST(x_coord AS float) AS Easting,
CAST(y_coord AS float) AS Northing,
dt_coordinate.facility_id,
sys_loc_code,
dt_coordinate.coord_type_code,
dt_coordinate.identifier,
observation_date,
x_coord,
y_coord,
elev,
dt_coordinate.elev_unit,
coord_zone,
elev_datum_code
FROM dt_coordinate
INNER JOIN dt_facility ON dt_coordinate.facility_id = dt_facility.facility_id
AND dt_coordinate.coord_type_code = dt_facility.coord_type_code
AND dt_facility.identifier = dt_coordinate.identifier
WHERE dt_facility.facility_id = @id
AND (dbo.fn_to_number(x_coord) IS NOT NULL)
AND (dbo.fn_to_number(y_coord) IS NOT NULL)
AND COALESCE (x_coord,
y_coord) IS NOT NULL
AND CAST(x_coord AS float) >= @dXMin
AND CAST(x_coord AS float) <= @dXMax
AND CAST(y_coord AS float) >= @dYMin
AND CAST(y_coord AS float) <= @dYMax ) c ON c.facility_id = l.facility_id
AND l.sys_loc_code = c.sys_loc_code
AND c.coord_type_code = f.coord_type_code
AND c.identifier = f.identifier
LEFT OUTER JOIN dt_well w ON l.facility_id = w.facility_id
AND l.sys_loc_code = w.sys_loc_code
LEFT OUTER JOIN
(SELECT l2.sys_loc_code slc,
STUFF(
(SELECT ', ' + l1.sys_loc_code
FROM dt_location l1
WHERE l1.parent_loc_code = l2.sys_loc_code
AND l1.facility_id = @ID
FOR XML PATH(''), TYPE) .value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') Colocated
FROM dt_location l2
WHERE l2.facility_id = @ID
GROUP BY l2.sys_loc_code) cl ON l.sys_loc_code = cl.slc
LEFT OUTER JOIN
(SELECT sys_loc_code,
facility_id,
AVG(depth) AS depth
FROM dt_water_table
GROUP BY sys_loc_code,
facility_id) wt ON l.facility_id = wt.facility_id
AND l.sys_loc_code = wt.sys_loc_code
LEFT OUTER JOIN rt_geologic_unit gu ON w.geologic_unit_code = gu.geologic_unit_code
LEFT OUTER JOIN rt_location_type lt ON lt.location_type_code = l.loc_type
LEFT OUTER JOIN
(SELECT ws.facility_id,
ws.sys_loc_code,
ws.start_depth AS ws_start_depth,
ws.end_depth AS ws_end_depth,
ws.depth_unit AS ws_depth_unit,
COALESCE('SCRN-'+LTRIM(STR(ws.start_depth, 12, 2))+'-'+LTRIM(STR(ws.end_depth, 12, 2)), '-') AS s_screen
FROM dt_well_segment ws
WHERE segment_type IN (@segment_type0)
UNION SELECT DISTINCT smpl.facility_id,
smpl.sys_loc_code,
smpl.start_depth,
smpl.end_depth,
smpl.depth_unit,
COALESCE(smpl.matrix_code + '_' +LTRIM(STR(smpl.start_depth, 12, 2))+'-'+LTRIM(STR(smpl.end_depth, 12, 2)), '-') AS s_screen
FROM dt_sample smpl
WHERE start_depth IS NOT NULL
AND smpl.matrix_code NOT IN (@mx0)) s ON s.facility_id = l.facility_id
AND s.sys_loc_code = l.sys_loc_code) inner_table
WHERE COALESCE(LOCATION, '') LIKE @sWell
AND Interval LIKE @sScreen
AND COALESCE(CLASS, '') LIKE @sClass
ORDER BY LOCATION,
[Top Depth] ASC "

"INSERT INTO Intervals ( Location, Interval, [Top Depth],[Bottom Depth],[Group]) Values (?, ?, ?, ?, ?)"

/////////////////////////////////////////////////

3. Obs - constituent / matrix where sample interval from DT_SAMPLE

"SELECT LOCATION, Interval, Constituent,
[Date],
[Top of Casing],
[Surface Elevation],
Media,
Flag,
Fraction,
[Detection Limit],
[Formatted Value],
[Value],
Northing,
Easting,
[Well Bottom Depth],
[Top Depth],
[Bottom Depth],
SampleID,
Azimuth,
Incline,
[Non-Detect],
[Duplicate]
FROM
(SELECT LOCATION, Interval, Constituent,
[Date],
Media,
Flag,
Fraction,
[Detection Limit],
[Formatted Value],
[Value],
SampleID,
[Non-Detect],
[Duplicate],
facility_id,
sys_loc_code,
[Top Depth],
[Bottom Depth]
FROM
(SELECT t.facility_id,
s.sys_loc_code,
COALESCE(s.sys_loc_code, s.sys_loc_code) AS LOCATION,
COALESCE(s.matrix_code + '_' +(LTRIM(STR(s.start_depth, 12, 2))+'-'+LTRIM(STR(s.end_depth, 12, 2))), '-') AS Interval,
CAST(dbo.fn_unit_conversion(COALESCE(s.start_depth, 0.0), COALESCE(s.depth_unit, f.elev_unit), COALESCE(f.elev_unit, s.depth_unit), NULL) AS float) AS [Top Depth],
CAST(dbo.fn_unit_conversion(COALESCE(s.end_depth, 0.0), COALESCE(s.depth_unit, f.elev_unit), COALESCE(f.elev_unit, s.depth_unit), NULL) AS float) AS [Bottom Depth],
s.sample_date AS [Date],
COALESCE(s.sample_name, s.sys_sample_code) AS SampleID,
a.chemical_name AS Constituent,
a.cas_rn,
COALESCE(s.matrix_code, '-') AS Media,
COALESCE(fraction, '-') AS Fraction,
(CASE
WHEN detect_flag = 'N'
OR detect_flag = 'n' THEN CAST(dbo.fn_unit_conversion(COALESCE(dbo.fn_to_number(r.reporting_detection_limit), dbo.fn_to_number(r.method_detection_limit), dbo.fn_to_number(r.quantitation_limit), 0.0), COALESCE(r.detection_limit_unit, r.result_unit, @reporting_unit0), @reporting_unit0, NULL)*@nd_multiplier AS float)
ELSE CAST(dbo.fn_unit_conversion(result_numeric, COALESCE(r.result_unit, @reporting_unit0), @reporting_unit0, NULL) AS float)
END) AS [Value],
(CASE
WHEN @reporting_unit0 = r.result_unit
AND r.result_text IS NOT NULL THEN r.result_text
ELSE ''
END) AS [Formatted Value],
CAST(dbo.fn_unit_conversion(COALESCE(CAST(r.reporting_detection_limit AS float), CAST(r.method_detection_limit AS float), CAST(r.quantitation_limit AS float), 0.0), COALESCE(r.detection_limit_unit, r.result_unit, @reporting_unit0), @reporting_unit0, NULL) AS float) AS [Detection Limit],
r.lab_qualifiers AS Flag,
r.result_unit,
r.detect_flag,
r.validated_yn,
(CASE
WHEN detect_flag = 'N'
OR detect_flag = 'n' THEN 'Y'
ELSE 'N'
END) AS [Non-Detect],
(CASE
WHEN sample_type_code = 'FD' THEN 'Y'
ELSE 'N'
END) AS [Duplicate],
s.sample_type_code,
s.task_code,
s.task_code_2,
result_type_code,
reportable_result,
t.lab_sdg,
t.column_number,
t.prep_method,
t.test_type,
t.analytic_method,
fs.field_sdg
FROM
(SELECT facility_id,
cas_rn,
lab_qualifiers,
result_unit,
detect_flag,
validated_yn,
result_numeric,
result_text,
reportable_result,
reporting_detection_limit,
method_detection_limit,
quantitation_limit,
detection_limit_unit,
result_type_code,
test_id
FROM dt_result
WHERE facility_id = @facility
AND cas_rn = @cas_rn0) r
INNER JOIN
(SELECT facility_id,
test_id,
lab_sdg,
column_number,
prep_method,
test_type,
analytic_method,
fraction,
sample_id
FROM dt_test
WHERE facility_id = @facility
AND fraction = @fraction0 ) t ON r.test_id = t.test_id
AND r.facility_id = t.facility_id
INNER JOIN
(SELECT sys_loc_code,
sample_name,
sys_sample_code,
sample_id,
facility_id,
sample_date,
matrix_code,
start_depth,
end_depth,
depth_unit,
sample_type_code,
task_code,
task_code_2
FROM dt_sample
WHERE facility_id = @facility
AND sys_loc_code IS NOT NULL
AND sample_date IS NOT NULL
AND matrix_code = @matrix0 ) s ON s.sample_id = t.sample_id
AND s.facility_id = r.facility_id
LEFT JOIN dt_field_sample fs ON s.sample_id = fs.sample_id
AND s.facility_id = fs.facility_id
INNER JOIN rt_analyte a ON r.cas_rn = a.cas_rn
INNER JOIN dt_facility f ON s.facility_id = f.facility_id
LEFT JOIN dt_location l ON l.facility_id = s.facility_id
AND l.sys_loc_code = s.sys_loc_code
LEFT JOIN dt_well w ON w.facility_id = s.facility_id
AND w.sys_loc_code = s.sys_loc_code) u
WHERE LOCATION LIKE @strWellName
AND ([Value] IS NOT NULL
OR [Formatted Value] <> '')
AND [Date] >= @start_date
AND [Date] <= @end_date
AND [Bottom Depth] + [Top Depth] >= @min_depth * 2
AND [Bottom Depth] + [Top Depth] <= @max_depth * 2 ) a
LEFT JOIN
(SELECT f.facility_id AS f_id,
l.loc_name,
l.sys_loc_code AS loc_code,
COALESCE(lt.location_type_desc, l.loc_desc, l.loc_type, '-') AS [Class],
c.x_coord AS Easting,
c.y_coord AS Northing,
COALESCE(gu.geologic_unit_desc, '-') AS Grp,
(CASE
WHEN COALESCE (l.total_depth,
w.depth_of_well,
0) = 0 THEN 0
ELSE CAST(dbo.fn_unit_conversion(COALESCE (l.total_depth, w.depth_of_well, 0), COALESCE (l.units, w.depth_unit, f.elev_unit), COALESCE(f.elev_unit, l.units, w.depth_unit), NULL) AS float)
END) AS [Well Bottom Depth],
COALESCE (l.total_depth,
w.depth_of_well) AS total_depth,
(CASE
WHEN dbo.fn_to_number(c.elev) IS NULL
AND @bUseDefaultElev = 0 THEN NULL
ELSE CAST(COALESCE(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev), COALESCE (c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float)
END) AS [Surface Elevation],
CAST(COALESCE(dbo.fn_unit_conversion(COALESCE(w.top_casing_elev, dbo.fn_to_number(c.elev)), COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, w.depth_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float) AS [Top of Casing],
COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,
COALESCE(dbo.fn_to_number(l.plunge), -90)+90 AS Incline
FROM dt_location l
INNER JOIN dt_facility f ON l.facility_id = f.facility_id
LEFT OUTER JOIN
(SELECT facility_id,
sys_loc_code,
coord_type_code,
observation_date,
identifier,
dbo.fn_to_number(x_coord) AS x_coord,
dbo.fn_to_number(y_coord) AS y_coord,
elev,
elev_unit,
coord_zone,
elev_datum_code
FROM dt_coordinate
WHERE facility_id = @facility
AND (dbo.fn_to_number(x_coord) IS NOT NULL)
AND (dbo.fn_to_number(y_coord) IS NOT NULL)) c ON c.facility_id = f.facility_id
AND l.sys_loc_code = c.sys_loc_code
AND c.coord_type_code = f.coord_type_code
AND c.identifier = f.identifier
LEFT OUTER JOIN dt_well w ON l.facility_id = w.facility_id
AND l.sys_loc_code = w.sys_loc_code
LEFT OUTER JOIN rt_geologic_unit gu ON w.geologic_unit_code = gu.geologic_unit_code
LEFT OUTER JOIN rt_location_type lt ON lt.location_type_code = l.loc_type
WHERE l.facility_id = @facility
AND c.x_coord >= @dxmin
AND c.x_coord <= @dxmax
AND c.y_coord >= @dymin
AND c.y_coord <= @dymax ) w ON w.f_id = a.facility_id
AND w.loc_code = a.sys_loc_code
WHERE [Surface Elevation] * 2 - ([Bottom Depth] + [Top Depth]) >= @min_elev * 2
AND [Surface Elevation] * 2 - ([Bottom Depth] + [Top Depth]) <= @max_elev * 2
AND [Surface Elevation] IS NOT NULL
ORDER BY [Well Bottom Depth] DESC,
LOCATION, Interval, Constituent,
Media,
Fraction,
[Date]"

Obs 2 - constituent / matrix where sample interval from DT_WELL_SEGMENT

"SELECT LOCATION, Interval, Constituent,
[Date],
[Top of Casing],
[Surface Elevation],
Media,
Flag,
Fraction,
[Detection Limit],
[Formatted Value],
[Value],
Northing,
Easting,
[Well Bottom Depth],
[Top Depth],
[Bottom Depth],
SampleID,
Azimuth,
Incline,
[Non-Detect],
[Duplicate]
FROM
(SELECT LOCATION, Interval, Constituent,
[Date],
Media,
Flag,
Fraction,
[Detection Limit],
[Formatted Value],
[Value],
SampleID,
[Non-Detect],
[Duplicate],
facility_id,
sys_loc_code,
[Top Depth],
[Bottom Depth]
FROM
(SELECT t.facility_id,
s.sys_loc_code,
COALESCE(s.sys_loc_code, s.sys_loc_code) AS LOCATION,
COALESCE('SCRN-'+LTRIM(STR(ws.start_depth, 12, 2))+'-'+LTRIM(STR(ws.end_depth, 12, 2)), '-') AS Interval,
CAST(dbo.fn_unit_conversion(COALESCE(ws.start_depth, 0.0), COALESCE(ws.depth_unit, f.elev_unit), COALESCE(f.elev_unit, ws.depth_unit), NULL) AS float) AS [Top Depth],
CAST(dbo.fn_unit_conversion(COALESCE(ws.end_depth, 0.0), COALESCE(ws.depth_unit, f.elev_unit), COALESCE(f.elev_unit, ws.depth_unit), NULL) AS float) AS [Bottom Depth],
s.sample_date AS [Date],
COALESCE(s.sample_name, s.sys_sample_code) AS SampleID,
a.chemical_name AS Constituent,
a.cas_rn,
COALESCE(s.matrix_code, '-') AS Media,
COALESCE(fraction, '-') AS Fraction,
(CASE
WHEN detect_flag = 'N'
OR detect_flag = 'n' THEN CAST(dbo.fn_unit_conversion(COALESCE(dbo.fn_to_number(r.reporting_detection_limit), dbo.fn_to_number(r.method_detection_limit), dbo.fn_to_number(r.quantitation_limit), 0.0), COALESCE(r.detection_limit_unit, r.result_unit, @reporting_unit0), @reporting_unit0, NULL)*@nd_multiplier AS float)
ELSE CAST(dbo.fn_unit_conversion(result_numeric, COALESCE(r.result_unit, @reporting_unit0), @reporting_unit0, NULL) AS float)
END) AS [Value],
(CASE
WHEN @reporting_unit0 = r.result_unit
AND r.result_text IS NOT NULL THEN r.result_text
ELSE ''
END) AS [Formatted Value],
CAST(dbo.fn_unit_conversion(COALESCE(CAST(r.reporting_detection_limit AS float), CAST(r.method_detection_limit AS float), CAST(r.quantitation_limit AS float), 0.0), COALESCE(r.detection_limit_unit, r.result_unit, @reporting_unit0), @reporting_unit0, NULL) AS float) AS [Detection Limit],
r.lab_qualifiers AS Flag,
r.result_unit,
r.detect_flag,
r.validated_yn,
(CASE
WHEN detect_flag = 'N'
OR detect_flag = 'n' THEN 'Y'
ELSE 'N'
END) AS [Non-Detect],
(CASE
WHEN sample_type_code = 'FD' THEN 'Y'
ELSE 'N'
END) AS [Duplicate],
sample_type_code,
s.task_code,
s.task_code_2,
result_type_code,
reportable_result,
t.lab_sdg,
t.column_number,
t.prep_method,
t.test_type,
t.analytic_method,
fs.field_sdg
FROM
(SELECT facility_id,
cas_rn,
lab_qualifiers,
result_unit,
detect_flag,
validated_yn,
result_numeric,
result_text,
reportable_result,
reporting_detection_limit,
method_detection_limit,
quantitation_limit,
detection_limit_unit,
result_type_code,
test_id
FROM dt_result
WHERE facility_id = @facility
AND cas_rn = @cas_rn0 ) r
INNER JOIN
(SELECT facility_id,
test_id,
lab_sdg,
column_number,
prep_method,
test_type,
analytic_method,
fraction,
sample_id
FROM dt_test
WHERE facility_id = @facility
AND fraction = @fraction0 ) t ON r.test_id = t.test_id
AND r.facility_id = t.facility_id
INNER JOIN
(SELECT sys_loc_code,
sample_name,
sys_sample_code,
sample_id,
facility_id,
sample_date,
matrix_code,
depth_unit,
sample_type_code,
task_code,
task_code_2
FROM dt_sample
WHERE facility_id = @facility
AND sample_date IS NOT NULL
AND sys_loc_code IS NOT NULL
AND matrix_code = @matrix0) s ON s.sample_id = t.sample_id
AND s.facility_id = r.facility_id
LEFT JOIN dt_field_sample fs ON s.sample_id = fs.sample_id
AND s.facility_id = fs.facility_id
INNER JOIN rt_analyte a ON r.cas_rn = a.cas_rn
INNER JOIN dt_facility f ON r.facility_id = f.facility_id
LEFT JOIN dt_location l ON l.facility_id = s.facility_id
AND l.sys_loc_code = s.sys_loc_code
LEFT JOIN dt_well w ON w.facility_id = s.facility_id
AND w.sys_loc_code = s.sys_loc_code
LEFT JOIN
(SELECT facility_id,
sys_loc_code,
start_depth,
end_depth,
depth_unit
FROM dt_well_segment
WHERE facility_id = @facility
AND segment_type IN (@segment_type0) ) ws ON ws.facility_id = r.facility_id
AND ws.sys_loc_code = s.sys_loc_code) u
WHERE LOCATION LIKE @strWellName
AND ([Value] IS NOT NULL
OR [Formatted Value] <> '')
AND [Date] >= @start_date
AND [Date] <= @end_date
AND [Bottom Depth] + [Top Depth] >= @min_depth * 2
AND [Bottom Depth] + [Top Depth] <= @max_depth * 2 ) a
LEFT JOIN
(SELECT f.facility_id AS f_id,
l.loc_name,
l.sys_loc_code AS loc_code,
COALESCE(lt.location_type_desc, l.loc_desc, l.loc_type, '-') AS [Class],
c.x_coord AS Easting,
c.y_coord AS Northing,
COALESCE(gu.geologic_unit_desc, '-') AS Grp,
(CASE
WHEN COALESCE (l.total_depth,
w.depth_of_well,
0) = 0 THEN 0
ELSE CAST(dbo.fn_unit_conversion(COALESCE (l.total_depth, w.depth_of_well, 0), COALESCE (l.units, w.depth_unit, f.elev_unit), COALESCE(f.elev_unit, l.units, w.depth_unit), NULL) AS float)
END) AS [Well Bottom Depth],
COALESCE (l.total_depth,
w.depth_of_well) AS total_depth,
(CASE
WHEN dbo.fn_to_number(c.elev) IS NULL
AND @bUseDefaultElev = 0 THEN NULL
ELSE CAST(COALESCE(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev), COALESCE (c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float)
END) AS [Surface Elevation],
CAST(COALESCE(dbo.fn_unit_conversion(COALESCE(w.top_casing_elev, dbo.fn_to_number(c.elev)), COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit, w.depth_unit, c.elev_unit, l.units), NULL), CAST(@dElevMax AS FLOAT)) AS float) AS [Top of Casing],
COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,
COALESCE(dbo.fn_to_number(l.plunge), -90)+90 AS Incline
FROM dt_location l
INNER JOIN dt_facility f ON l.facility_id = f.facility_id
LEFT OUTER JOIN
(SELECT facility_id,
sys_loc_code,
coord_type_code,
observation_date,
identifier,
dbo.fn_to_number(x_coord) AS x_coord,
dbo.fn_to_number(y_coord) AS y_coord,
elev,
elev_unit,
coord_zone,
elev_datum_code
FROM dt_coordinate
WHERE facility_id = @facility
AND (dbo.fn_to_number(x_coord) IS NOT NULL)
AND (dbo.fn_to_number(y_coord) IS NOT NULL)) c ON c.facility_id = f.facility_id
AND l.sys_loc_code = c.sys_loc_code
AND c.coord_type_code = f.coord_type_code
AND c.identifier = f.identifier
LEFT OUTER JOIN dt_well w ON l.facility_id = w.facility_id
AND l.sys_loc_code = w.sys_loc_code
LEFT OUTER JOIN rt_geologic_unit gu ON w.geologic_unit_code = gu.geologic_unit_code
LEFT OUTER JOIN rt_location_type lt ON lt.location_type_code = l.loc_type
WHERE l.facility_id = @facility
AND c.x_coord >= @dxmin
AND c.x_coord <= @dxmax
AND c.y_coord >= @dymin
AND c.y_coord <= @dymax ) w ON w.f_id = a.facility_id
AND w.loc_code = a.sys_loc_code
WHERE [Surface Elevation] * 2 - ([Bottom Depth] + [Top Depth]) >= @min_elev * 2
AND [Surface Elevation] * 2 - ([Bottom Depth] + [Top Depth]) <= @max_elev * 2
AND [Surface Elevation] IS NOT NULL
ORDER BY [Well Bottom Depth] DESC,
LOCATION, Interval, Constituent,
Media,
Fraction,
[Date]"

"INSERT INTO Observations ( Location, Interval, [Date], [Value], [Formatted Value], Constituent, Flag, Fraction, Media, [Detection Limit], Duplicate, [Non-Detect] ) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

/////////////////////////////////////////////////

4. Constituents

"SELECT rt_analyte.chemical_name AS [Constituent],

COALESCE(alp.matrix, al.matrix, '-') AS Media,

COALESCE(alp.fraction, al.fraction, '-') AS Fraction,

al.action_level_code AS [Standard Name],

(CASE

WHEN COALESCE(alp.matrix, al.matrix, '-') = '-'

OR COALESCE(alp.fraction, al.fraction, '-') = '-' THEN CAST(action_level AS float)

ELSE CAST(dbo.fn_unit_conversion(CAST(action_level AS float), COALESCE(alp.unit, @reporting_unit0), @reporting_unit0, NULL) AS float)

END) AS [Standard],

(CASE

WHEN action_level_min IS NULL THEN NULL

ELSE (CASE

WHEN COALESCE(alp.matrix, al.matrix, '-') = '-'

OR COALESCE(alp.fraction, al.fraction, '-') = '-' THEN CAST(action_level_min AS float)

ELSE CAST(dbo.fn_unit_conversion(CAST(action_level_min AS float), COALESCE(alp.unit, @reporting_unit0), @reporting_unit0, CAST(action_level_min AS float)) AS float)

END)

END) AS [Standard Min]

FROM

(SELECT matrix,

fraction,

action_level_code

FROM dt_action_level

WHERE (facility_id IS NULL

OR facility_id = @id)

AND action_level_code IN ('B-30 TCE') ) al

INNER JOIN

(SELECT param_code,

action_level_code,

action_level,

action_level_min,

unit,

matrix,

fraction

FROM dt_action_level_parameter

WHERE action_level IS NOT NULL

AND param_code = @cas_rn0) alp ON al.action_level_code = alp.action_level_code

INNER JOIN rt_analyte ON rt_analyte.cas_rn = alp.param_code

WHERE (COALESCE(alp.matrix, al.matrix, '-') =@matrix0

OR (alp.matrix IS NULL

AND al.matrix IS NULL))

AND (COALESCE(alp.fraction, al.fraction, '-') = @fraction0

OR (alp.fraction IS NULL

AND al.fraction IS NULL))

UNION

SELECT rt_analyte.chemical_name AS [Constituent],

COALESCE(alp.matrix, al.matrix, '-') AS Media,

COALESCE(alp.fraction, al.fraction, '-') AS Fraction,

al.action_level_code AS [Standard Name],

(CASE

WHEN COALESCE(alp.matrix, al.matrix, '-') = '-'

OR COALESCE(alp.fraction, al.fraction, '-') = '-' THEN CAST(action_level AS float)

ELSE CAST(dbo.fn_unit_conversion(CAST(action_level AS float), COALESCE(alp.unit, @reporting_unit1), @reporting_unit1, NULL) AS float)

END) AS [Standard],

(CASE

WHEN action_level_min IS NULL THEN NULL

ELSE (CASE

WHEN COALESCE(alp.matrix, al.matrix, '-') = '-'

OR COALESCE(alp.fraction, al.fraction, '-') = '-' THEN CAST(action_level_min AS float)

ELSE CAST(dbo.fn_unit_conversion(CAST(action_level_min AS float), COALESCE(alp.unit, @reporting_unit1), @reporting_unit1, CAST(action_level_min AS float)) AS float)

END)

END) AS [Standard Min]

FROM

(SELECT matrix,

fraction,

action_level_code

FROM dt_action_level

WHERE (facility_id IS NULL

OR facility_id = @id)

AND action_level_code IN ('B-30 TCE') ) al

INNER JOIN

(SELECT param_code,

action_level_code,

action_level,

action_level_min,

unit,

matrix,

fraction

FROM dt_action_level_parameter

WHERE action_level IS NOT NULL

AND param_code = @cas_rn1) alp ON al.action_level_code = alp.action_level_code

INNER JOIN rt_analyte ON rt_analyte.cas_rn = alp.param_code

WHERE (COALESCE(alp.matrix, al.matrix, '-') =@matrix1

OR (alp.matrix IS NULL

AND al.matrix IS NULL))

AND (COALESCE(alp.fraction, al.fraction, '-') = @fraction1

OR (alp.fraction IS NULL

AND al.fraction IS NULL))"

"INSERT INTO [Constituents] ( Constituent, [Media], [Fraction], [Units], [Equivalent Weight], [Standard Name], [Standard]) Values (?, ?, ?, ?, ?, ?, ?)"

/////////////////////////////////////////////////

5. Borings

"SELECT * FROM (SELECT l.sys_loc_code AS Location_ID,

l.sys_loc_code AS Location,

l.loc_type AS Class,

l.loc_desc,

COALESCE(material_desc, lth.material_name) AS Strata,

Cast(lth.start_depth AS FLOAT) AS [Top Depth],

Cast(lth.end_depth AS FLOAT) AS [Bottom Depth],

COALESCE (l.total_depth, w.depth_of_well) AS total_depth,

Cast(c.x_coord AS FLOAT) AS Easting,

Cast(c.y_coord AS FLOAT) AS Northing,

f.coord_unit AS units,

( CASE

WHEN dbo.fn_to_number(c.elev) IS NULL

AND @bUseDefaultElev = 0 THEN NULL

ELSE Cast(COALESCE(dbo.fn_unit_conversion(

dbo.fn_to_number(c.elev),

COALESCE (c.elev_unit,

l.units,

f.elev_unit),

COALESCE(f.elev_unit,c.elev_unit,l.units),

NULL), CAST(@dElevMax AS FLOAT)

) AS

FLOAT)

END ) AS [Surface Elevation],

CAST(COALESCE( dbo.fn_unit_conversion(w.top_casing_elev,

COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit),

COALESCE(f.elev_unit,w.depth_unit,c.elev_unit),

NULL), CAST(@dElevMax AS FLOAT)) AS FLOAT) AS [Top of Casing],

COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,

( COALESCE(dbo.fn_to_number(l.plunge), -90)

+ 90 ) AS Incline,

c.coord_zone,

c.elev_datum_code,

w.geologic_unit_code AS Grp,

lth.remark_1,

lth.remark_2,

lth.material_name,

lth.geo_unit_code_1,

lth.geo_unit_code_2,

lth.geo_unit_code_3,

lth.geo_unit_code_4,

lth.geo_unit_code_5,

lth.moisture,

lth.permeable,

lth.color,

lth.observation,

lth.grainsize,

lth.odor,

lth.custom_field_1,

lth.custom_field_2,

lth.custom_field_3,

lth.custom_field_4,

lth.custom_field_5

FROM (SELECT * FROM dt_location

WHERE facility_id = @id AND ( status_flag = 'A' OR status_flag IS NULL )

AND COALESCE(loc_type,'') LIKE @sClass ) l

INNER JOIN dt_facility f

ON l.facility_id = f.facility_id

LEFT OUTER JOIN (SELECT facility_id,

sys_loc_code,

coord_type_code,

observation_date,

identifier,

x_coord,

y_coord,

elev,

elev_unit,

coord_zone,

elev_datum_code

FROM dt_coordinate

WHERE dbo.fn_to_number(x_coord) IS NOT NULL

AND dbo.fn_to_number(y_coord) IS NOT NULL

) c

ON c.facility_id = l.facility_id

AND l.sys_loc_code = c.sys_loc_code

AND c.coord_type_code = f.coord_type_code

AND c.identifier = f.identifier

LEFT OUTER JOIN dt_well w

ON l.facility_id = w.facility_id

AND l.sys_loc_code = w.sys_loc_code

INNER JOIN (SELECT l.*,

COALESCE((SELECT Min(start_depth)

FROM dt_lithology l2

WHERE l2.facility_id =

l.facility_id

AND l2.sys_loc_code =

l.sys_loc_code

AND l2.start_depth >

l.start_depth),

(SELECT

total_depth

FROM

dt_location gl

WHERE

gl.facility_id = l.facility_id

AND gl.sys_loc_code = l.sys_loc_code), (SELECT

depth_of_well

FROM

dt_well w

WHERE

w.facility_id = l.facility_id

AND w.sys_loc_code =

l.sys_loc_code)) AS end_depth

FROM dt_lithology l

WHERE facility_id = @id ) lth

ON lth.sys_loc_code = l.sys_loc_code

LEFT OUTER JOIN rt_material

ON lth.material_name = rt_material.material_name

) inner_table WHERE COALESCE(Location, '') LIKE @sWell AND Easting >= @dXMin AND Easting <= @dXMax AND Northing >= @dYMin AND Northing <= @dYMax ORDER BY Location, [Top Depth] ASC"

"INSERT INTO Borings (Location, Strata, [Top Depth], [Bottom Depth]) Values (?, ?, ?, ?)"

/////////////////////////////////////////////////

6. Stratigraphy

"SELECT * FROM (SELECT l.sys_loc_code AS Location_ID,

l.sys_loc_code AS Location,

l.loc_type AS Class,

l.loc_desc,

COALESCE(geologic_unit_desc, lth.geo_unit_code_1) AS Strata,

Cast(lth.start_depth AS FLOAT) AS [Top Depth],

Cast(lth.end_depth AS FLOAT) AS [Bottom Depth],

COALESCE (l.total_depth, w.depth_of_well) AS total_depth,

Cast(c.x_coord AS FLOAT) AS Easting,

Cast(c.y_coord AS FLOAT) AS Northing,

f.coord_unit AS units,

( CASE

WHEN dbo.fn_to_number(c.elev) IS NULL

AND @bUseDefaultElev = 0 THEN NULL

ELSE Cast(COALESCE(dbo.fn_unit_conversion(

dbo.fn_to_number(c.elev),

COALESCE (c.elev_unit,

l.units,

f.elev_unit),

COALESCE(f.elev_unit,c.elev_unit,l.units),

NULL), CAST(@dElevMax AS FLOAT)

) AS

FLOAT)

END ) AS [Surface Elevation],

CAST(COALESCE( dbo.fn_unit_conversion(w.top_casing_elev,

COALESCE (w.depth_unit, c.elev_unit, l.units, f.elev_unit),

COALESCE(f.elev_unit,w.depth_unit,c.elev_unit),

NULL), CAST(@dElevMax AS FLOAT)) AS FLOAT) AS [Top of Casing],

COALESCE(dbo.fn_to_number(l.bearing), 0) AS Azimuth,

( COALESCE(dbo.fn_to_number(l.plunge), -90)

+ 90 ) AS Incline,

c.coord_zone,

c.elev_datum_code,

w.geologic_unit_code AS Grp,

lth.remark_1,

lth.remark_2,

lth.material_name,

lth.geo_unit_code_1,

lth.geo_unit_code_2,

lth.geo_unit_code_3,

lth.geo_unit_code_4,

lth.geo_unit_code_5,

lth.moisture,

lth.permeable,

lth.color,

lth.observation,

lth.grainsize,

lth.odor,

lth.custom_field_1,

lth.custom_field_2,

lth.custom_field_3,

lth.custom_field_4,

lth.custom_field_5

FROM (SELECT * FROM dt_location

WHERE facility_id = @id AND ( status_flag = 'A' OR status_flag IS NULL )

AND COALESCE(loc_type,'') LIKE @sClass ) l

INNER JOIN dt_facility f

ON l.facility_id = f.facility_id

LEFT OUTER JOIN (SELECT facility_id,

sys_loc_code,

coord_type_code,

observation_date,

identifier,

x_coord,

y_coord,

elev,

elev_unit,

coord_zone,

elev_datum_code

FROM dt_coordinate

WHERE dbo.fn_to_number(x_coord) IS NOT NULL

AND dbo.fn_to_number(y_coord) IS NOT NULL

) c

ON c.facility_id = l.facility_id

AND l.sys_loc_code = c.sys_loc_code

AND c.coord_type_code = f.coord_type_code

AND c.identifier = f.identifier

LEFT OUTER JOIN dt_well w

ON l.facility_id = w.facility_id

AND l.sys_loc_code = w.sys_loc_code

INNER JOIN (SELECT l.*,

COALESCE((SELECT Min(start_depth)

FROM dt_lithology l2

WHERE l2.facility_id =

l.facility_id

AND l2.sys_loc_code =

l.sys_loc_code

AND l2.start_depth >

l.start_depth),

(SELECT

total_depth

FROM

dt_location gl

WHERE

gl.facility_id = l.facility_id

AND gl.sys_loc_code = l.sys_loc_code), (SELECT

depth_of_well

FROM

dt_well w

WHERE

w.facility_id = l.facility_id

AND w.sys_loc_code =

l.sys_loc_code)) AS end_depth

FROM dt_lithology l

WHERE facility_id = @id ) lth

ON lth.sys_loc_code = l.sys_loc_code

LEFT OUTER JOIN rt_geologic_unit

ON lth.geo_unit_code_1 = rt_geologic_unit.geologic_unit_code

) inner_table WHERE COALESCE(Location, '') LIKE @sWell AND Easting >= @dXMin AND Easting <= @dXMax AND Northing >= @dYMin AND Northing <= @dYMax ORDER BY Location, [Top Depth] ASC"

"INSERT INTO Stratigraphy (Location, Strata, [Top Depth], [Bottom Depth]) Values (?, ?, ?, ?)"

/////////////////////////////////////////////////

7. Point Values

"SELECT p.sys_loc_code AS Location,

Cast(c.x_coord AS FLOAT) AS Easting,

Cast(c.y_coord AS FLOAT) AS Northing,

CAST(depth AS FLOAT) AS Depth,

param AS Constituent,

CAST(param_value AS FLOAT) AS Value,

'' AS [Text Value],

l.loc_type AS Class,

CASE WHEN end_depth IS NULL THEN CAST(0.0 AS FLOAT) ELSE CAST(end_depth - depth AS FLOAT) END AS Thickness,

Cast(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev),

COALESCE (c.elev_unit, l.units, f.elev_unit), COALESCE(f.elev_unit,c.elev_unit,l.units), NULL

) AS

FLOAT) AS [Surface Elevation]

FROM dt_downhole_point_data p

INNER JOIN dt_facility f

ON f.facility_id = p.facility_id

INNER JOIN dt_location l

ON l.facility_id = p.facility_id

AND l.sys_loc_code = p.sys_loc_code

INNER JOIN (SELECT facility_id,

sys_loc_code,

x_coord,

y_coord,

elev,

elev_unit,

coord_type_code,

identifier

FROM dt_coordinate

WHERE ( dbo.fn_to_number(x_coord) IS NOT NULL )

AND ( dbo.fn_to_number(y_coord) IS NOT NULL )) c

ON c.facility_id = p.facility_id

AND c.sys_loc_code = p.sys_loc_code

AND f.coord_type_code = c.coord_type_code

AND f.identifier = c.identifier

WHERE p.facility_id = @facility_id

AND depth IS NOT NULL

AND l.loc_type LIKE @class

AND p.sys_loc_code LIKE @well ORDER BY p.sys_loc_code,p.param,p.depth DESC"

"INSERT INTO [Point Values] (Location, Constituent, [Depth], [Value], [Text Value], [Thickness]) Values (?, ?, ?, ?, ?, ?)"

/////////////////////////////////////////////////

8. Fill

"SELECT ws.sys_loc_code AS Location, ws.material_type_code AS Material,

COALESCE(ws.remark,t.material_type_desc,ws.material_type_code) AS Notes,

(CASE WHEN diameter_unit IS NOT NULL THEN CAST(dbo.fn_unit_conversion(COALESCE (ws.outer_diameter,ws.inner_diameter,1),ws.diameter_unit,'in',NULL) AS float) ELSE Cast(COALESCE(ws.outer_diameter,ws.inner_diameter,1) AS Float) END) AS Diameter,

Cast(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev),

COALESCE (c.elev_unit, f.elev_unit), COALESCE(f.elev_unit, c.elev_unit), NULL) AS FLOAT) AS [Surface Elevation],

Cast(dbo.fn_unit_conversion(w.top_casing_elev,

COALESCE (w.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit, w.depth_unit, c.elev_unit), NULL) AS FLOAT) AS [Top of Casing],

Cast(dbo.fn_unit_conversion(COALESCE(ws.start_depth, 0),

COALESCE (ws.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit, ws.depth_unit, c.elev_unit), NULL) AS FLOAT) AS [Top Depth],

Cast(dbo.fn_unit_conversion(COALESCE(ws.end_depth, 0),

COALESCE (ws.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit, ws.depth_unit, c.elev_unit), NULL) AS FLOAT) AS [Bottom Depth]

FROM (SELECT facility_id, remark,

sys_loc_code,

material_type_code,

segment_type,

outer_diameter,

inner_diameter,

diameter_unit,

start_depth,end_depth,

depth_unit

FROM dt_well_segment

WHERE facility_id = @id AND segment_type IN (

'BENTONITE','Fill','GROUTED ANNULUS','UPPER SEAL','WELL POINT PACK') ) ws

INNER JOIN dt_facility f

ON ws.facility_id = f.facility_id

LEFT OUTER JOIN rt_well_segment_type t ON t.segment_type = ws.segment_type AND t.material_type_code = ws.material_type_code

LEFT OUTER JOIN dt_coordinate c

ON c.facility_id = f.facility_id

AND ws.sys_loc_code = c.sys_loc_code

AND c.coord_type_code = f.coord_type_code

AND c.identifier = f.identifier

LEFT OUTER JOIN dt_well w

ON ws.facility_id = w.facility_id

AND ws.sys_loc_code = w.sys_loc_code

ORDER BY Location ASC, [Bottom Depth] ASC"

"INSERT INTO Fill (Location, Material, [Top Depth], [Bottom Depth], Diameter) Values (?, ?, ?, ?, ?)"

/////////////////////////////////////////////////

9. Well Construction

"SELECT ws.sys_loc_code AS LOCATION,
(CASE
WHEN ws.segment_type IN (@segment_type0) THEN 'SCREEN'
ELSE ws.segment_type
END) AS Material,
(CASE
WHEN diameter_unit IS NOT NULL THEN CAST(dbo.fn_unit_conversion(COALESCE (ws.outer_diameter, ws.inner_diameter, 1),ws.diameter_unit, 'in', NULL) AS float)
ELSE Cast(COALESCE(ws.outer_diameter, ws.inner_diameter, 1) AS Float)
END) AS Diameter,

COALESCE(ws.remark,t.material_type_desc,ws.material_type_code) AS Notes,

Cast(dbo.fn_unit_conversion(dbo.fn_to_number(c.elev),

COALESCE (c.elev_unit, f.elev_unit), COALESCE(f.elev_unit,c.elev_unit), NULL) AS FLOAT) AS [Surface Elevation],

Cast(dbo.fn_unit_conversion(w.top_casing_elev,

COALESCE (w.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit,w.depth_unit,c.elev_unit), NULL) AS FLOAT) AS [Top of Casing],

Cast(dbo.fn_unit_conversion(COALESCE(ws.start_depth, 0),

COALESCE (ws.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit,ws.depth_unit,c.elev_unit), NULL) AS FLOAT) AS [Top Depth],

Cast(dbo.fn_unit_conversion(COALESCE(ws.end_depth, 0),

COALESCE (ws.depth_unit, c.elev_unit, f.elev_unit), COALESCE(f.elev_unit,ws.depth_unit,c.elev_unit), NULL) AS FLOAT) AS [Bottom Depth]

FROM dt_well_segment ws

INNER JOIN dt_facility f

ON ws.facility_id = f.facility_id

LEFT OUTER JOIN rt_well_segment_type t ON t.segment_type = ws.segment_type AND t.material_type_code = ws.material_type_code

LEFT OUTER JOIN dt_coordinate c

ON c.facility_id = f.facility_id

AND ws.sys_loc_code = c.sys_loc_code

AND c.coord_type_code = f.coord_type_code

AND c.identifier = f.identifier

LEFT OUTER JOIN dt_well w

ON ws.facility_id = w.facility_id

AND ws.sys_loc_code = w.sys_loc_code

WHERE ws.facility_id = @id AND ws.segment_type

IN (

'CASING','Cover','PAD','RISER','SCREEN','Surface Casing')ORDER BY Location ASC, [Bottom Depth] ASC"

"INSERT INTO [Well Construction] (Location, Material, [Top Depth], [Bottom Depth], Diameter) Values (?, ?, ?, ?, ?)"