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?
In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!