DT_RESULT

<< Click to Display Table of Contents >>

 >Live > Agents > Transformation Agents > Data Deriver > Examples > exportSqlProcedure >

DT_RESULT

The following SERIES_FUNCTION_INFO calculates the monthly (frequency) average (value) of the DT_LOGGER_SERIES with LOGGER_SERIES_ID 32101231:

 

 

{
sourceSeriesId: [
  32101231
],
frequency: "mm",
dateOffset: -60,
value: "AVG(?)",
exportSqlProcedure: "equis.copy_logger_datum_to_result_temp_c"
}

 

 

-60 seconds (dateOffset) is added to each derived DT_LOGGER_DATUM.DATUM_UTC_DT (e.g., 2019-07-01 00:00:00 becomes 2019-06-30 23:59:00), bringing the monthly average back into the month that it is for. (Note: This data is exported to DT_RESULT, which requires DT_SAMPLE and DT_TEST rows, and DT_SAMPLE.SAMPLE_DATE and DT_TEST.ANALYSIS_DATE are SMALLDATETIMEs, which are rounded to the nearest minute.)

 

After the agent has finished deriving the monthly averages, and added the derived data to DT_LOGGER_DATUM, it executes the equis.copy_logger_datum_to_result_temp_c stored procedure (exportSqlProcedure).

 

The equis.copy_logger_datum_to_result_temp_c stored procedure takes the input parameters that the Data Deriver Agent provides, and adds the additional input parameters that the equis.copy_logger_datum_to_result stored procedure needs.

 

The equis.copy_logger_datum_to_result stored procedure copies the derived data from DT_LOGGER_DATUM to DT_RESULT (and also to DT_SAMPLE and DT_TEST). A NULL value is allowed for some of its input parameters (e.g., FACILITY_ID and SYS_LOC_CODE are obtained from the series, or logger, row if they are NULL, and DT_RESULT allows a NULL value in RESULT_UNIT), however, not all parameters can be NULL (e.g., DT_RESULT does NOT allow NULL values in CAS_RN).

 

 

IF OBJECT_ID(N'[equis].[copy_logger_datum_to_result_temp_c]') IS NOT NULL

DROP PROCEDURE [equis].[copy_logger_datum_to_result_temp_c];

GO

 

CREATE PROCEDURE [equis].[copy_logger_datum_to_result_temp_c]

(

 @deriver_logger_series_id BIGINT

,@deriver_start_date DATETIME2(0)

,@deriver_end_date DATETIME2(0)

,@deriver_update_yn CHAR

)

AS

BEGIN

EXEC equis.copy_logger_datum_to_result @deriver_logger_series_id, @deriver_start_date, @deriver_end_date, NULL, NULL, 'B', 'AIR', 'FIELD', 'N', 'NA', 'INITIAL', 'TEMP-C', 'TRG', 'DEG C';

END

 

 

 

IF OBJECT_ID(N'[equis].[copy_logger_datum_to_result]') IS NOT NULL

DROP PROCEDURE [equis].[copy_logger_datum_to_result];

GO

 

CREATE PROCEDURE [equis].[copy_logger_datum_to_result]

(

  @logger_series_id BIGINT -- dt_logger_datum, PK, FK (dt_logger_series), NOT NULL

,@start_date DATETIME2(0) -- dt_logger_datum.datum_utc_dt, PK, NOT NULL

,@end_date DATETIME2(0) -- dt_logger_datum.datum_utc_dt, PK, NOT NULL

,@facility_id BIGINT -- dt_sample/dt_test/dt_result, PK, FK (dt_facility), NOT NULL

,@sys_loc_code VARCHAR(20) -- dt_sample, FK (dt_location), NULL

,@sample_type_code VARCHAR(20) -- dt_sample, FK (rt_sample_type), NULL

,@matrix_code VARCHAR(10) -- dt_sample/dt_test.lab_matrix_code, FK (rt_matrix), NULL

,@analytic_method VARCHAR(20) -- dt_test, FK (rt_analytic_method), NOT NULL

,@fraction VARCHAR(10) -- dt_test, FK (rt_fraction), NULL

,@column_number VARCHAR(2) -- dt_test, NULL

,@test_type VARCHAR(10) -- dt_test, FK (rt_test_type), NULL

,@cas_rn VARCHAR(15) -- dt_result, PK, FK (rt_analyte), NOT NULL

,@result_type_code VARCHAR(10) -- dt_result, FK (rt_result_type), NOT NULL

,@result_unit VARCHAR(15) -- dt_result, FK (rt_unit.unit_code), NULL

)

AS

BEGIN

IF @facility_id IS NULL

BEGIN

  SELECT @facility_id = COALESCE(s.facility_id, l.facility_id)

  FROM dt_logger_series s

  INNER JOIN dt_logger l ON l.logger_id = s.logger_id

  WHERE s.logger_series_id = @logger_series_id;

END

 

IF @sys_loc_code IS NULL

BEGIN

  SELECT @sys_loc_code = COALESCE(s.sys_loc_code, l.sys_loc_code)

  FROM dt_logger_series s

  INNER JOIN dt_logger l ON l.logger_id = s.logger_id

  WHERE s.logger_series_id = @logger_series_id;

END

 

DECLARE @datum TABLE

(

    datum_utc_dt DATETIME2(0)

  ,datum_value REAL

  ,sys_sample_code VARCHAR(40)

  ,sample_id BIGINT

  ,test_id BIGINT

);

 

-- e.g., (datum_utc_dt, datum_value, sys_sample_code) = ('2001-02-03 04:05:06', 1.23, '123456,2001-02-03T04:05:06')

INSERT INTO @datum (datum_utc_dt, datum_value, sys_sample_code)

SELECT datum_utc_dt, datum_value, CONVERT(VARCHAR, logger_series_id) + ',' + CONVERT(VARCHAR, datum_utc_dt, 126)

FROM dt_logger_datum

WHERE logger_series_id = @logger_series_id AND datum_utc_dt BETWEEN @start_date AND @end_date;

 

-- dt_sample

UPDATE d

SET sample_id = s.sample_id

FROM @datum d

INNER JOIN dt_sample s ON s.sys_sample_code = d.sys_sample_code;

 

DECLARE @new_id_count BIGINT;

DECLARE @new_id_start BIGINT;

DECLARE @id BIGINT;

 

SELECT @new_id_count = COUNT(*) FROM @datum WHERE sample_id IS NULL;

 

IF @new_id_count > 0

BEGIN

  EXEC equis.get_ids @new_id_count, @new_id_start OUTPUT;

  SET @id = @new_id_start - 1;

 

  UPDATE @datum

  SET sample_id = @id, @id = @id + 1

  WHERE sample_id IS NULL;

 

  INSERT INTO dt_sample (facility_id, sample_id, sys_sample_code, sys_loc_code, sample_type_code, sample_date, matrix_code)

  SELECT @facility_id, d.sample_id, d.sys_sample_code, @sys_loc_code, @sample_type_code, d.datum_utc_dt, @matrix_code

  FROM @datum d

  WHERE d.sample_id BETWEEN @new_id_start AND @new_id_start + @new_id_count - 1;

END

 

-- dt_test

UPDATE d

SET test_id = t.test_id

FROM @datum d

INNER JOIN dt_test t ON t.sample_id = d.sample_id;

 

SELECT @new_id_count = COUNT(*) FROM @datum WHERE test_id IS NULL;

 

IF @new_id_count > 0

BEGIN

  EXEC equis.get_ids @new_id_count, @new_id_start OUTPUT;

  SET @id = @new_id_start - 1;

 

  UPDATE @datum

  SET test_id = @id, @id = @id + 1

  WHERE test_id IS NULL;

 

  INSERT INTO dt_test (facility_id, test_id, sample_id, analytic_method, analysis_date, fraction, column_number, test_type, lab_matrix_code)

  SELECT @facility_id, d.test_id, d.sample_id, @analytic_method, d.datum_utc_dt, @fraction, @column_number, @test_type, @matrix_code

  FROM @datum d

  WHERE d.test_id BETWEEN @new_id_start AND @new_id_start + @new_id_count - 1;

END

 

-- dt_result

MERGE INTO dt_result r

USING @datum d

ON r.facility_id = @facility_id AND r.test_id = d.test_id AND r.cas_rn = @cas_rn

WHEN MATCHED THEN

  UPDATE SET r.result_numeric = d.datum_value, r.result_type_code = @result_type_code, r.result_unit = @result_unit

WHEN NOT MATCHED THEN

  INSERT (facility_id, test_id, cas_rn, result_numeric, result_type_code, result_unit)

  VALUES (@facility_id, d.test_id, @cas_rn, d.datum_value, @result_type_code, @result_unit);

END