Live Data Deriver Agent

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

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

-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_FLOW, and DT_FLOW.MEASUREMENT_START_DATE is a SMALLDATETIME, which is 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_flow stored procedure (exportSqlProcedure), which copies the derived data from DT_LOGGER_DATUM to DT_FLOW.

Note, it is necessary to grant EXECUTE permission to 'Public' in the database to allow users to execute the stored procedure below.

Create stored procedure [equis].[copy_logger_datum_to_flow]:

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

DROP PROCEDURE [equis].[copy_logger_datum_to_flow];

CREATE PROCEDURE [equis].[copy_logger_datum_to_flow]

(

@deriver_logger_series_id BIGINT

,@deriver_start_date DATETIME2(0)

,@deriver_end_date DATETIME2(0)

,@deriver_update_yn CHAR

)

BEGIN

DECLARE @facility_id INT;

DECLARE @sys_loc_code VARCHAR(20);

SELECT @facility_id = COALESCE(s.facility_id, l.facility_id), @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 = @deriver_logger_series_id;

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);

END

Live Data Deriver Agent – DT_FLOW

Live Data Deriver Agent – DT_FLOW