Export EQuIS Data Queries

<< Click to Display Table of Contents >>

EQuIS 7  >>  EnviroInsite > Reports > Export Database >

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 (?, ?, ?, ?, ?)"