SQL querying data that has been compressed
Posted on 2009-07-13
I have a problem with trending some SQL data.
The data table [TableInputs] is structured as follows
The data table is filled with live data. It has been "compressed" when being created such that if [Value] is a duplicate of previous [Tag] and previous [timestamp] it is not added e.g. the following live data would create 3 records (not 4)
The data is compressed as there are many values that do not change often e.g. once per hour / once per day and the data is recorded minutely.
1/1/9 00:00 ABC 123
*1/1/9 00:10 ABC 123 'this value would NOT be stored as it is duplicate of value of previous timestamp for same [Tag] 00:00
1/1/9 01:02 ABC 621
1/1/9 01:03 ABC 789
...more data with [Tag] e.g. DEF (and other tags)
This is fine however there is a problem when creating tables or plotting charts on multiple [Tag] over a fixed period of time. The user can pick a number of [Tags] to plot between at set [Timestamp[
When querying the data for a selected period e.g.
SELECT * FROM TableInputs WHERE [Timetamp]>=@StartTime and [Timestamp]' <=@EndTime AND [Tag]='ABC'
some data can be "missing" at start e.g. in above sample if user wanted to query
1/1/9 00:11 to 1/1/9 01:03
for Tag='ABC' there would be a large "gap" at start.
Instead of this gap I'd like first value for 1/1/9 00:11 to be 123 (this is the value of 'ABC' at 1/1/9, it just has not been recorded as data is compressed).
I hope the scenario makes sense and would much appreciate help in solving it?
Thanks in advance