Step-by-step example

<< Click to Display Table of Contents >>

EQuIS 7  >>  Enterprise > EQuIS Live > Agents > Data Deriver > Examples > SERIES_FUNCTION_INFO >

Step-by-step example

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

 

 

{
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)"
}

 

 

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:

 

DATUM_UTC_DT

DATUM_VALUE

2017-11-19 00:00:10

1822259

2017-11-19 00:15:06

1822282

2017-11-19 00:30:05

1822304

...

...

2017-11-19 23:45:05

1824250

2017-11-20 00:00:06

1824268

2017-11-20 01:00:00

1824333

 

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.

 

End Date

2017-11-19 00:06:00

2017-11-20 00:06:00

 

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

 

Start Date

End Date

2017-11-18 00:06:00

2017-11-19 00:06:00

2017-11-19 00:06:00

2017-11-20 00:06:00

 

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

 

Start Date

End Date

Value

2017-11-18 00:06:00

2017-11-19 00:06:00

0 (= 1822259 - 1822259)

2017-11-19 00:06:00

2017-11-20 00:06:00

1986 (= 1824268 - 1822282)

 

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"):

 

End Date / DATUM_UTC_DT

Value / DATUM_VALUE

2017-11-18 23:59:59

0

2017-11-19 23:59:59

1986

 

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