<< Click to Display Table of Contents >> Navigation:  Live > Agents > Transformation Agents > Data Deriver > Examples > SERIES_FUNCTION_INFO > Live Data Deriver Agent – Step-by-Step Function Example

The following SERIES_FUNCTION_INFO calculates the daily (frequency) positive range (value) of the DT_LOGGER_SERIES with LOGGER_SERIES_ID 31871972:

 

 

This example assumes that the agent is calculating the data for the first time (that there is no derived / calculated datum present in DT_LOGGER_DATUM for the derived series), and that the DT_LOGGER_DATUM for LOGGER_SERIES_ID 31871972 ("source datum") is:

 

 

1.Create a table of end dates and times:

a.Create a table, and add to it all dates and times, for each day (frequency), with the time set to the beginning of each day (e.g., ... 2017-11-19 00:00:00, 2017-11-20 00:00:00, ...).

b.Add -utcOffset hours to each date and time. (utcOffset is 0 in this example. It overrides the derived series' -DT_LOGGER.UTC_OFFSET_HRS, e.g. 5. Note: DATEADD(hh, @utc_offset_hrs, ...) is used to add the -utcOffset hours, or -UTC_OFFSET_HRS, which truncates fractional hours.)

c.Add endDate (e.g., 6 minutes) to each date and time (e.g., ... 2017-11-19 00:06:00, 2017-11-20 00:06:00, ...).

d.Remove all dates and times that are less than the source datum's min DATUM_UTC_DT (e.g., 2017-11-19 00:00:10), or greater than the source datum's max DATUM_UTC_DT (e.g., 2017-11-20T01:00:00). Note: Derived datum is only calculated if ALL of the necessary source datum is present (e.g., all of the necessary source datum is present for 2017-11-20 00:06:00 because a source datum row exists with a DATUM_UTC_DT that's greater than or equal to it) bumped up to the next hour (i.e., 2017-11-20 01:00:00). If there is more than one source series, then all of the necessary source datum must be present for ALL of the source series.

 

 

2.Add a start date and time for each end date and time. Set the start date and time equal to the end date and time minus one day (frequency), or use startDate if it is defined (e.g., DATEADD(dd, -1, ?) where ? is equal to the end date and time). (Note: This example does not need to define startDate "DATEADD(dd, -1, ?)" because negative one day (frequency) is the default, if not defined.)

 

 

3.Calculate a value for each start and end date range, using the value SQL statement and the source datum. (Note: This example uses the default includeStartDate and includeEndDate: the end dates are inclusive, but the start dates are not.)

 

 

4.Add dateOffset (e.g., -361 seconds) to each end date and time. (Note: This is done after the calculations are completed, and does not affect the values.)

 

5.Insert the end dates and times (as DATUM_UTC_DTs) and values (as DATUM_VALUEs) into the DT_LOGGER_DATUM table, with LOGGER_SERIES_ID equal to the derived series' LOGGER_SERIES_ID ("derived datum"):

 

 

6.(If deleteInputDataAfter is defined, e.g. 100, then source datum older than 100 days (deleteInputDataAfter times frequency) is deleted.)

 

7.(If exportSql, or exportSqlProcedure, is defined, it is executed. For example, you can use it to copy the derived datum from DT_LOGGER_DATUM to another table such as DT_WATER_LEVEL.)

 

8.The next time that the agent runs, it will calculate values starting with the day after the already derived datum's max DATUM_UTC_DT; it does not recalculate derived datum values. (Note: If the source datum changes, you can use the Data Deriver Update Agent to recalculate the derived datum values.)