NULL or 0

The series contains source/raw data. (The Data Deriver Agent does NOT process these series.)

1

The series is dependent on one or more series with derivation equal to NULL or 0.

2

The series is dependent on one or more series with derivation equal to 1.

...

N

The series is dependent on one or more series with derivation equal to N - 1.

sourceSeriesId

An array of integers containing one or more LOGGER_SERIES_ID values that represent the input data for the derived series. The input series may come directly from a data logger or it may be another reduced data series (e.g., reduce minute data to hourly data, reduce hourly data to daily data).

frequency

A string that is the desired frequency of the derived data, which must be an optional integer coefficient followed by one of the following:

•mi: One value every minute (e.g., "15mi" would derive one value for each 15 minute period).

•hh: One value every hour (e.g., "12hh" would derive one value for each 12 hour period).

•dd: One value every day (i.e., daily).

•wk: One value every week (i.e., weekly).

•mm: One value every month (i.e., monthly).

•yy: One value every year (i.e., annual).

Regardless of which frequency value is used, the calculated value will be reported at the end of each time period. For example, if the calculated data is an hourly (i.e., hh) average of 60-second data, then the hourly data reported for each hour (e.g., 08:00) represents the average of all input values during the preceding hour (07:01-08:00).

Use the appropriate coefficient to match the desired data frequency. For example, if the raw data is reported every fifteen minutes and you want the derived data to have the same fifteen-minute frequency, then use 15mi as the frequency value. If the derived series has a higher frequency than the raw data, then the derived series will contain null values where raw data is missing. Even though each derived value will be calculated from only one input value, the value setting must include an aggregate function. In this example, you may use MAX(?) as the value, since the MAX of one input value is exactly that value.

startDate

A string that is an SQL expression used to calculate the beginning of the time period for which raw data is aggregated. The question mark (?) represents the date/time of the calculated value (i.e., DATUM_UTC_DT). If this parameter is omitted, the default will be based on the frequency. Can be inclusive or exclusive, with the default set to exclusive. Examples include:

•dateadd(hh,-1,?): Use raw data from the hour previous to each calculated value.

•dateadd(hh,-24,?): Use raw data from the 24 hours previous to each calculated value (e.g., a rolling 24-hour sum that is calculated hourly).

•(select max(dt) from ST_CALENDAR c where c.month = 4 and c.day = 1 and c.dt <= GETDATE()): Use raw data from a known date in the current year (e.g., a rolling annual sum from 01-Apr to current date/time).

endDate

A string that is an SQL expression used to calculate the end of the time period for which raw data is aggregated. Can be inclusive or exclusive, with the default set to inclusive.

declare

A string that declares and sets one or more user-defined SQL variables (e.g., @depth and @angle).

The user-defined SQL variables can be set using any of the predefined SQL variables (e.g., @target_series_id), and they can be used to calculate the value.

value

A string that is a SQL expression used to calculate the actual derived value. The question mark (?) represents the raw values (i.e., DATUM_VALUE). The expression may be applied to multiple input values within a time period, so it must include some aggregate function. Examples include:

•SUM(?): The sum of all input values for the given period (appropriate for precipitation and other similar data).

•AVG(?): The average of all input values for the given period.

•MAX(?): The maximum of all input values for the given period.

•MIN(?): The minimum of all input values for the given period.

•COUNT(?): The count of input values for the given period.

•STDEV(?): The statistical standard deviation of all input values for the given period.

•VAR(?): The statistical variance of all input values in the specified expression.

•CAST(STUFF(MIN(CONVERT(VARCHAR, D.DATUM_UTC_DT,126) + CONVERT(VARCHAR,D.DATUM_VALUE)), 1, 19, '') AS REAL): The first (chronologically) input datum for the given period (other values are ignored); use MAX to get the last input datum.

•SUM(?) / 2.5 + 19: Sum the input values and apply the given multiplier and constant to correct data.

deleteInputDataAfter

An integer that represents how long the input data should be retained before being deleted from the database. The unit for this value is frequency. Omit this value to keep input data indefinitely (input data will not be deleted).

customFunction

True or false; default is false. Set to true when your value expression is the name of the user-defined scalar function for obtaining data value.

includeStartDate

True or false; default is false. Determines if start date of the interval is inclusive.

includeEndDate

True or false; default is true. Determines if end date of the interval is inclusive.

dateOffset

Offset of the derived data point (in seconds) from original intervals derived date value. Default is 0. Use negative values to move point back in time positive to move into future from the original date.

utcOffset

UTC offset of time zone for the logger (in hours). By default, agent uses DT_LOGGER.UTC_OFFSET_HRS to determine what time zone the logger is in. NULL value in table results in 0 offset. This parameter can be used to override the value in DT_LOGGER.

exportSQL

Optional SQL INSERT statement to execute after data derivation is completed. It should contain the following WHERE conditions (to prevent copying of older data, and data from other series):

•DT_LOGGER_DATUM.DATUM_UTC_DT > @deriver_start_date

•DT_LOGGER_DATUM.LOGGER_SERIES_ID = @deriver_logger_series_id

exportSqlProcedure

Optional, if the exportSQL becomes too long, or too complex, you can add it to a stored procedure. The stored procedure must have the following input parameters (see Example: exportSqlProcedure:"equis.my_custom_procedure"):

•@deriver_logger_series_id INT

•@deriver_start_date DATETIME

•@deriver_end_date DATETIME

•@deriver_update_yn CHAR

round

Optional, round the derived series' DT_LOGGER_DATUM.DATUM_VALUEs to this number of digits; positive values round to the right side of the decimal point, while negative values round to the left side of the decimal point. To truncate instead of round, add a comma and a one to this value. Examples:

•"round":3 (e.g., rounds 123.456789 to 123.457)

•"round":0 (e.g., rounds 123.456789 to 123)

•"round":-1 (e.g., rounds 123.456789 to 120)

•"round":"3,1" (e.g., truncates 123.456789 to 123.456)