Delta retrieval - handling NULL values

The initial NULL value after a non-NULL is always returned. Multiple NULL values are suppressed. The first non-NULL after a NULL is always returned even if it is the same as the previous non-NULL value.

SELECT TagName, DateTime, Value, QualityDetail

FROM History

WHERE TagName = 'A001'

AND DateTime >= '2009-09-12 00:20'

AND DateTime <= '2009-09-12 00:40'

AND wwRetrievalMode = 'Delta'

This query can be run against the following sample data:

The following is a graphical representation of the data:

The results are:

The sample data points and the results are mapped on the following chart. Only the data falling between the time start and end marks at 00:20 and 00:40 (shown on the chart as dark vertical lines) are returned by the query.

Because there is no value that matches the start time, an initial value at 00:20 is returned in the results based on the value of the preceding data point at 00:16. Because there is no change in the value at 00:27 from the value at 00:24, the data point appears on the chart but does not appear in the results. Similarly, the two 0.0 values at 00:33 and 00:35 are also excluded from the results. However, the non-NULL value at 00:36 is returned, even though it is the same as the value at 00:28, because it represents a delta from the preceding (NULL) value at 00:35.