Live Data Computation Agent – Example 2

<< Click to Display Table of Contents >>

Navigation:  Live > Agents > Transformation Agents > Data Computation > Examples >

Live Data Computation Agent – Example 2

The following SERIES_FUNCTION_INFO does what Example 1's value does, but using a stored procedure, and no value.

 

 

{
"sourceSeriesId": [
  130041, 130032, 130033
],
"procedure": "EXEC [equis].[sp_insert_dt_logger_datum_plus_measure] @target_series_id, @source_series_ids, @start_date, @end_date, @facility_id, @sys_loc_code;"
}

 

 

Note: The INSERT INTO @temp_datum... below is similar to how the Data Computation Agent handles the value SERIES_FUNCTION_INFO parameter. In this example the value is MAX(datum_value + equis.fn_logger_measure_datum(@facility_id, @sys_loc_code, datum_utc_dt)).

 

 

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

DROP PROCEDURE [equis].[sp_insert_dt_logger_datum_plus_measure];

GO

 

CREATE PROCEDURE [equis].[sp_insert_dt_logger_datum_plus_measure]

(

  @target_series_id BIGINT

,@source_series_ids VARCHAR(100)

,@start_date       DATETIME2(0)

,@end_date         DATETIME2(0)

,@facility_id       BIGINT

,@sys_loc_code     VARCHAR(20)

)

AS

BEGIN

DECLARE @source_series_ids_table TABLE

(

   id BIGINT

);

 

INSERT INTO

   @source_series_ids_table (id)

SELECT DISTINCT

   TRY_CAST(id.value AS BIGINT)

FROM

   equis.split(@source_series_ids) AS id;

 

DECLARE @temp_datum TABLE

(

    datum_utc_dt DATETIME2(0)

  ,datum_value REAL

);

 

INSERT INTO

   @temp_datum (datum_utc_dt, datum_value)

SELECT

    datum_utc_dt

  ,MAX(datum_value + equis.fn_logger_measure_datum(@facility_id, @sys_loc_code, datum_utc_dt))

FROM

   dt_logger_datum

WHERE

       logger_series_id IN (SELECT id FROM @source_series_ids_table)

  AND datum_utc_dt BETWEEN @start_date AND @end_date

GROUP BY

   datum_utc_dt;

 

MERGE

   dt_logger_datum d

USING

   @temp_datum t

ON

       d.logger_series_id = @target_series_id

  AND d.datum_utc_dt = t.datum_utc_dt

WHEN MATCHED THEN

  UPDATE SET d.datum_value = t.datum_value

WHEN NOT MATCHED THEN

  INSERT (logger_series_id, datum_utc_dt, datum_value)

  VALUES (@target_series_id, t.datum_utc_dt, t.datum_value);

END

 

 

Note: The stored procedure will be executed even if the SAVE changes to database? report input parameter is unchecked. The stored procedure may use @save_changes_to_database_yn, and only save changes to the database if 'Y'.