Live Data Computation Agent

The following SERIES_FUNCTION_INFO computes a datum value for each source series (with LOGGER_SERIES_IDs 130041, 130032, and 130033) datum (distinct) date-time:

Computed DT_LOGGER_DATUM.DATUM_VALUE = source DT_LOGGER_DATUM.DATUM_VALUE + equis.fn_logger_measure_datum(...).

If there is more than 1 source datum with the same DATUM_UTC_DT, it uses the MAX value.

The equis.sp_insert_dt_water_level_daily_max() stored procedure calculates the computed data's daily max value and adds it to DT_WATER_LEVEL.

{
"sourceSeriesId": [
130041, 130032, 130033
],
"value": "MAX(d.datum_value + equis.fn_logger_measure_datum(@facility_id, @sys_loc_code, d.datum_utc_dt))",
"procedure": "EXEC [equis].[sp_insert_dt_water_level_daily_max] @target_series_id, @start_date, @end_date, @facility_id, @sys_loc_code;"
}

This example assumes that the DT_LOGGER_DATUM rows for the source datum are:

LOGGER_SERIES_ID	DATUM_UTC_DT	DATUM_VALUE
130041	2018-06-27 01:23:45	123.4
130041	2018-06-28 09:45:00	197.64
130032	2018-06-28 09:45:00	207.3
	...	...
130041	2018-07-02 13:34:32	170.97
130033	2018-07-02 13:34:33	163
130041	2018-07-03 12:00:00	182.7
130041	2018-07-05 23:13:32	190.313

And the DT_LOGGER_MEASURE rows (where FACILITY_ID and SYS_LOC_CODE are the same as the computed series') are:

DATUM_UTC_DT	DATUM_VALUE
2018-06-23 07:13:00	70.2
2018-06-30 03:23:00	77.9
...	...
2018-07-03 12:00:00	75.3

And that there is no existing computed datum (nothing to delete, or update; only insert).

1.Create a table of date-times for the computed datum; add each distinct source datum date-time:

DATUM_UTC_DT
2018-06-27 01:23:45
2018-06-28 09:45:00
...
2018-07-02 13:34:32
2018-07-02 13:34:33
2018-07-03 12:00:00
2018-07-05 23:13:32

Note: There is only ONE 2018-06-28 09:45:00; duplicate date-times in the source datum are not added.

2.Calculate the datum values (computed datum value = MAX(source datum value + equis.fn_logger_measure_datum(...))):

DATUM_UTC_DT	DATUM_VALUE
2018-06-27 01:23:45	123.4 + 70.2 = 193.6
2018-06-28 09:45:00	MAX(197.64 + 70.2, 207.3 + 70.2) = 277.5
...	...
2018-07-02 13:34:32	170.97 + 77.9 = 248.87
2018-07-02 13:34:33	163 + 77.9 = 240.9
2018-07-03 12:00:00	182.7 + 75.3 = 258
2018-07-05 23:13:32	190.313 + 75.3 = 265.613

3.Insert computed datum into DT_LOGGER_DATUM (set LOGGER_SERIES_ID = @target_series_id).

4.Execute equis.sp_insert_dt_water_level_daily_max() stored procedure.

Note: The equis.fn_logger_measure_datum function is a simplified version of the equis.measure_datum function (already available in EQuIS); with only the 'step', no 'linear'.

-- Looks up the start datum date (the most recent datum prior to (or on) the measurement_date),

-- and returns the start datum value (the most recent datum prior to the measurement_date).

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

DROP FUNCTION [equis].[fn_logger_measure_datum];

CREATE FUNCTION [equis].[fn_logger_measure_datum]

(

@facility_id INT

,@sys_loc_code VARCHAR(20)

,@measurement_date SMALLDATETIME

)

RETURNS REAL

BEGIN

DECLARE @start_datum_date SMALLDATETIME;

DECLARE @datum_value REAL;

-- Lookup the start datum date (the most recent datum prior to (or on) the measurement_date).

SELECT

@start_datum_date = MAX(start_date)

FROM

dt_measure_datum

WHERE

facility_id = @facility_id

AND sys_loc_code = @sys_loc_code

AND start_date <= @measurement_date;

-- Return the start datum value (the most recent datum prior to the measurement_date).

SELECT

@datum_value = datum_value

FROM

dt_measure_datum

WHERE

facility_id = @facility_id

AND sys_loc_code = @sys_loc_code

AND start_date = @start_datum_date;

RETURN @datum_value;

END

-- Inserts/updates daily max dt_logger_datum (datum_utc_dt, datum_value)

-- into dt_water_level.

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

DROP PROCEDURE [equis].[sp_insert_dt_water_level_daily_max];

CREATE PROCEDURE [equis].[sp_insert_dt_water_level_daily_max]

(

@target_series_id BIGINT

,@start_date DATETIME2(0)

,@end_date DATETIME2(0)

,@facility_id BIGINT

,@sys_loc_code VARCHAR(20)

)

BEGIN

DECLARE @temp_datum TABLE

(

datum_utc_dt DATE

,datum_value REAL

);

-- Get daily max.

INSERT INTO

@temp_datum (datum_utc_dt, datum_value)

SELECT

CAST(datum_utc_dt AS DATE)

,MAX(datum_value)

FROM

dt_logger_datum

WHERE

logger_series_id = @target_series_id

AND datum_utc_dt BETWEEN @start_date AND @end_date

GROUP BY

CAST(datum_utc_dt AS DATE);

-- dt_water_level.measurement_date, water_level_depth = @datum.datum_utc_dt, datum_value

MERGE

dt_water_level w

USING

@temp_datum t

w.facility_id = @facility_id

AND w.sys_loc_code = @sys_loc_code

AND w.measurement_date = t.datum_utc_dt

WHEN MATCHED THEN

UPDATE SET w.water_level_depth = t.datum_value

WHEN NOT MATCHED THEN

INSERT (facility_id, sys_loc_code, measurement_date, water_level_depth)

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

END

Live Data Computation Agent – Example 1

Live Data Computation Agent – Example 1