exportSqlProcedure

<< Click to Display Table of Contents >>

EQuIS 7  >>  Enterprise > EQuIS Live > Agents > Data Deriver > Examples >

exportSqlProcedure

After the agent has finished processing a derived series and added its derived data to DT_LOGGER_DATUM, it executes the derived series' exportSql or exportSqlProcedure (if exportSql or exportSqlProcedure are defined in the derived series' SERIES_FUNCTION_INFO). The exportSql or exportSqlProcedure can be used to, for example, copy the derived data from DT_LOGGER_DATUM to another table, or to do something else with the derived data.

 

The export SQL statements can be added to the derived series' SERIES_FUNCTION_INFO (e.g., exportSql:"INSERT dt_flow ..."). But the SERIES_FUNCTION_INFO has a maximum allowed length, which limits the length of the SQL statements that you can add. And the SQL statements may be complex, and more difficult to understand, if they are in the SERIES_FUNCTION_INFO. An alternative is to add the export SQL statements to a stored procedure, and add the stored procedure's name to the SERIES_FUNCTION_INFO (e.g., exportSqlProcedure:"equis.my_custom_procedure").

 

The derived DT_LOGGER_DATUM rows have LOGGER_SERIES_IDs that are equal to the derived series' LOGGER_SERIES_ID (@deriver_logger_series_id), and DATUM_UTC_DTs that are greater than @deriver_start_date and smaller than @deriver_end_date. The Data Deriver Agent sets @deriver_end_date equal to SQL Server's maximum date and time, and @deriver_update_yn equal to 'n'. And the Date Deriver Update Agent sets @deriver_end_date equal to the user provided end date, and @deriver_update_yn equal to 'y'.

 

 

-- Adds the derivation results to DT_LOGGER_REMARK,

-- and copies the derived data from DT_LOGGER_DATUM to DT_FLOW and DT_WATER_LEVEL.

 

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

DROP PROCEDURE [equis].[my_custom_procedure];

GO

 

CREATE PROCEDURE [equis].[my_custom_procedure]

(

 @deriver_logger_series_id INT     -- the ID of the derived series; the series for which the calculation was performed

,@deriver_start_date       DATETIME -- the start date and time for the calculation; data is > this date

,@deriver_end_date         DATETIME -- the end date and time for the calculation; data is < this date

,@deriver_update_yn         CHAR     -- 'Y' if calculation was performed by Data Deriver Update Agent - data between start and end dates may overlap with existing data - else 'N' if calculation was performed by Data Deriver Agent - all data between start and end dates is new

)

AS

BEGIN

DECLARE @facility_id INT;

DECLARE @sys_loc_code VARCHAR(20);

DECLARE @logger_id INT;

 

-- Get the derived series' FACILITY_ID, SYS_LOC_CODE, and LOGGER_ID.

SELECT TOP 1

    @facility_id = COALESCE(ls.facility_id, l.facility_id)

  ,@sys_loc_code = COALESCE(ls.sys_loc_code, l.sys_loc_code)

  ,@logger_id = l.logger_id

FROM

   dt_logger_series ls

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

WHERE

   ls.logger_series_id = @deriver_logger_series_id;

 

DECLARE @datum_value_count INT;

DECLARE @min_datum_utc_dt DATETIME;

DECLARE @max_datum_utc_dt DATETIME;

DECLARE @min_datum_value REAL;

DECLARE @max_datum_value REAL;

 

-- Get the derivation results: number of datum rows added, min and max dates and times, and min and max values.

SELECT

    @datum_value_count = COUNT(*)

  ,@min_datum_utc_dt = MIN(datum_utc_dt)

  ,@max_datum_utc_dt = MAX(datum_utc_dt)

  ,@min_datum_value = MIN(datum_value)

  ,@max_datum_value = MAX(datum_value)

FROM

   dt_logger_datum

WHERE

       logger_series_id = @deriver_logger_series_id

  AND datum_utc_dt > @deriver_start_date

  AND datum_utc_dt < @deriver_end_date;

 

IF @datum_value_count > 0

BEGIN

  IF NOT EXISTS(SELECT * FROM dt_logger_remark WHERE logger_id = @logger_id AND logger_series_id = @deriver_logger_series_id AND remark_utc_dt = @min_datum_utc_dt)

  BEGIN

    DECLARE @logger_remark_id INT;

    EXEC equis.get_ids 1, @logger_remark_id OUTPUT;

 

    DECLARE @duration_s INT = DATEDIFF(SECOND, @min_datum_utc_dt, @max_datum_utc_dt);

 

    DECLARE @remark VARCHAR(2048) = FORMATMESSAGE('Derived @ %s : deriver_update_yn = %s; dt_logger_datum COUNT = %d; datum_utc_dt MIN - MAX : %s - %s; datum_value MIN - MAX : %s - %s',

    CONVERT(NVARCHAR(MAX), GETUTCDATE(), 120),

     @deriver_update_yn,

     @datum_value_count,

    CONVERT(NVARCHAR(MAX), @min_datum_utc_dt, 120), CONVERT(NVARCHAR(MAX), @max_datum_utc_dt, 120),

    LTRIM(STR(@min_datum_value, 30, 3)), LTRIM(STR(@max_datum_value, 30, 3)));

 

    -- Add the derivation results to DT_LOGGER_REMARK.

    INSERT INTO dt_logger_remark (logger_remark_id, logger_id, logger_series_id, remark_utc_dt, duration_s, remark)

    VALUES (@logger_remark_id, @logger_id, @deriver_logger_series_id, @min_datum_utc_dt, @duration_s, @remark);

  END

 

  -- Copy the derived data FROM DT_LOGGER_DATUM to DT_FLOW.

  MERGE dt_flow f

  USING dt_logger_datum d

  ON (f.facility_id = @facility_id AND f.sys_loc_code = @sys_loc_code AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date AND f.measurement_start_date = d.datum_utc_dt)

  WHEN MATCHED THEN

    UPDATE SET f.gauge = d.datum_value

  WHEN NOT MATCHED BY TARGET AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date THEN

    INSERT (facility_id, sys_loc_code, measurement_start_date, gauge)

    VALUES (@facility_id, @sys_loc_code, d.datum_utc_dt, d.datum_value);

 

  -- Copy the derived data FROM DT_LOGGER_DATUM to DT_WATER_LEVEL.

  MERGE dt_water_level w

  USING dt_logger_datum d

  ON (w.facility_id = @facility_id AND w.sys_loc_code = @sys_loc_code AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date AND w.measurement_date = d.datum_utc_dt)

  WHEN MATCHED THEN

    UPDATE SET w.water_level_depth = d.datum_value

  WHEN NOT MATCHED BY TARGET AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date THEN

    INSERT (facility_id, sys_loc_code, measurement_date, water_level_depth)

    VALUES (@facility_id, @sys_loc_code, d.datum_utc_dt, d.datum_value);

END

END