<< Click to Display Table of Contents >> Navigation:  Live > Agents > Transformation Agents > Data Deriver > Examples > Live Data Deriver Agent – SERIES_FUNCTION_INFO

•{ "sourceSeriesId": [31871972], "frequency": "dd", "utcOffset": 0, "startDate": "DATEADD(dd, -1, ?)", "endDate": "DATEADD(mi, 6, ?)", "dateOffset": -361, "value": "MAX(CASE WHEN d.datum_value < 0 THEN NULL ELSE d.datum_value END) - MIN(CASE WHEN d.datum_value < 0 THEN NULL ELSE d.datum_value END)" }
 
See explanation here.

 

•{ "sourceSeriesId": [1234, 2345, 3456], "frequency": "hh", "startDate": "dateadd(hh,-1,?)", "value": "SUM(?)", "deleteInputDataAfter": 168 }
 
Using the given three input series, create an hourly sum and delete the original data after 168 hours (aka one week).

 

•{ "sourceSeriesId": [1234, 2345], "frequency": "hh", "startDate": "dateadd(hh,-24,?)", "value": "SUM(?)" }
 
Using the given two input series, create a rolling 24-hour sum that is calculated each hour (deleteInputDataAfter is omitted, so never delete the input data).

 

•{ "sourceSeriesId": [3456], "frequency": "dd", "startDate": "dateadd(yy,datediff(yy,0,?),0)", "value": "SUM(?)" }
 
Using the given input series, calculate a daily value that is the year-to-date (since 01-Jan) sum (never delete the input data).

 

•{ "sourceSeriesId": [2345], "frequency": "hh", "value": "SUM(?) + 16.1" }
 
Using the given input series, calculate an hourly value that applies a simple correction formula; startDate is omitted so it will default to each preceding hour.

 

•{ "sourceSeriesId": [2345], "frequency": "15mi", "startDate": "CASE WHEN DATEPART(mi, ?) = 0 THEN DATEADD(hh, -1, ?) ELSE DATEADD(mi, -DATEPART(mi, ?), ?) END", "value": "SUM(?)" }
 

Using the given input series, calculate the sum of the 15 minute data for each whole hour cumulatively, then drop back to zero to start the next hour.

 

•{ "sourceSeriesId": [2345], "frequency": "dd", "startDate": "DATEADD(dd, -1, ?)", "endDate": "DATEADD(hh, 12, ?)", "value": "SUM(?)" }
 

Using the given input series, calculate the sum over a 24-hour period from midday to midday.

 

•{ "sourceSeriesId": [2345], "frequency": "hh", "exportSQL": "INSERT dt_flow (measurement_start_date, instant_flow, facility_id, sys_loc_code) SELECT datum_utc_dt, datum_value, 1, 'Location1' FROM dt_logger_datum WHERE dt_logger_datum.logger_series_id = @deriver_logger_series_id AND dt_logger_datum.datum_utc_dt > @deriver_start_date" }
 
Using the given input series, calculate an hourly value and copy the data into DT_FLOW for FACILITY_ID=1 and SYS_LOC_CODE="Location1".

 

•{ "sourceSeriesId": [2345], "frequency": "hh", "exportSqlProcedure": "equis.my_custom_procedure" }
 
Using the given input series, calculate an hourly value, and then call stored procedure equis.my_custom_procedure (see Example: exportSqlProcedure:"equis.my_custom_procedure").

 

•{ "sourceSeriesId": [10111316], "frequency": "hh", "value": "AVG(CASE WHEN d.datum_qualifier = 'bad ' THEN NULL ELSE d.datum_value END)" }
 
Exclude source DATUM_VALUE from being used in the derivation, when its DATUM_QUALIFER = 'bad '.

 

•{ "sourceSeriesId": [46869], "frequency": "dd", "declare": "DECLARE @depth REAL, @angle REAL; SELECT @depth = CAST(custom_field_1 AS REAL), @angle = CAST(custom_field_2 AS REAL) FROM dt_logger_series WHERE logger_series_id = @target_series_id;", "value": "AVG((@depth/SIN(@angle)) - (?/9.81))" }
 
User-defined SQL variables @depth and @angle are declared, set to the computed series' (SQL variable: @target_series_id) CUSTOM_FIELD_1 and CUSTOM_FIELD_2, and used to calculate the value.Using the given input series, calculate the sum over a 24-hour period from mid-day to mid-day.

 

•{ "sourceSeriesId": [300003, 300013], "frequency": "dd", "value": "AVG(CASE WHEN d.logger_series_id = 300003 THEN ? ELSE NULL END) - AVG(CASE WHEN d.logger_series_id = 300013 THEN ? ELSE NULL END)" }
 
Average daily difference between series 300003 and 300013 datum.