Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

SQL querying data that has been compressed

Hi

I have a problem with trending some SQL data.

The data table [TableInputs] is structured as follows
[Timestamp]      datetime
[Tag]                  varchar
[Value]              String

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
from
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
0
rwallacej
Asked:
rwallacej
2 Solutions
 
rob_farleyCommented:
So your data looks like:

create table [TableInputs] ([Timestamp] datetime, [Tag] varchar(10), [Value] int);
--int not string? varchar(10) not varchar?

insert TableInputs (Timestamp, Tag, Value)
values
 ('1/1/9 00:00', 'ABC', 123)
,('1/1/9  01:02', 'ABC', 621)
,('1/1/9  01:03', 'ABC', 789)
;

So then this query should do the trick I think...

declare @StartTime datetime, @EndTime datetime;
set @StartTime = '1/1/9 00:10';
set @EndTime = '1/1/9 01:02:30';


select
      case
            when Timestamp < @StartTime then @StartTime
            when Timestamp > @EndTime then @EndTime
            else Timestamp
      end as Timestamp, Value
from
      (
      select *
      from TableInputs
      where Timestamp >= @StartTime and TimeStamp < @EndTime
      and Tag = 'ABC'

      union all
      select * from (
            select top (1) * from TableInputs where Tag = 'ABC' and Timestamp < @StartTime order by Timestamp desc
      ) t

      union all
      select * from (
            select top (1) * from TableInputs where Tag = 'ABC' and Timestamp >= @EndTime order by Timestamp asc
      ) t
) u
order by Timestamp;

Here I've included a row to cover the start of the period, and one to cover the end of the period. You'll notice I've used the CASE statement to handle those extra rows (if they appear at all).

Rob


0
 
Mohed SharfiCommented:
Hi rwallacej,
how are you, please try to use this function(snippet) after creation to make sure your datetime range is ok and try to use this Query:
SELECT * FROM TableInputs WHERE [Timetamp] between dbo.format(@StartTime,'d/m/y HH:NN') and dbo.format(@EndTime,'d/m/y HH:NN') AND [Tag]='ABC'
Note:
- please sure if your Timetamp is datetime
- you can change your format as any option (see the function format eg:Unix timestamp)
thanks
CREATE FUNCTION dbo.FormatDateTime 
( 
    @dt DATETIME, 
    @format VARCHAR(16) 
) 
RETURNS VARCHAR(64) 
AS 
BEGIN 
    DECLARE @dtVC VARCHAR(64) 
    SELECT @dtVC = CASE @format 
 
    WHEN 'LONGDATE' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATEANDTIME' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11) 
 
    WHEN 'SHORTDATE' THEN 
 
        LEFT(CONVERT(CHAR(19), @dt, 0), 11) 
 
    WHEN 'SHORTDATEANDTIME' THEN 
 
        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
 
    WHEN 'UNIXTIMESTAMP' THEN 
 
        CAST(DATEDIFF(SECOND, '19700101', @dt) 
        AS VARCHAR(64)) 
 
    WHEN 'YYYYMMDD' THEN 
 
        CONVERT(CHAR(8), @dt, 112) 
 
    WHEN 'YYYY-MM-DD' THEN 
 
        CONVERT(CHAR(10), @dt, 23) 
 
    WHEN 'YYMMDD' THEN 
 
        CONVERT(VARCHAR(8), @dt, 12) 
 
    WHEN 'YY-MM-DD' THEN 
 
        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 
        5, 0, '-'), 3, 0, '-') 
 
    WHEN 'MMDDYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
 
    WHEN 'MM-DD-YY' THEN 
 
        CONVERT(CHAR(8), @dt, 10) 
 
    WHEN 'MM/DD/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 1) 
 
    WHEN 'MM/DD/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 101) 
 
    WHEN 'DDMMYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
 
    WHEN 'DD-MM-YY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
 
    WHEN 'DD/MM/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 3) 
 
    WHEN 'DD/MM/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 103) 
 
    WHEN 'HH:MM:SS 24' THEN 
 
        CONVERT(CHAR(8), @dt, 8) 
 
    WHEN 'HH:MM 24' THEN 
 
        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
 
    WHEN 'HH:MM:SS 12' THEN 
 
        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
 
    WHEN 'HH:MM 12' THEN 
 
        LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
 
    ELSE 
 
        'Invalid format specified' 
 
    END 
    RETURN @dtVC 
END 
 
GO

Open in new window

0
 
rob_farleyCommented:
mSharfi,

I don't think his problem is with formatting. He has already said that his data is using the datetime type.

Rob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you actually need a "calendar" kind of table, which stored just, but all the time values possible for the reports.
this can be filled easily, if you need help with that...

then, I would create a function/procedure to extract the rows needed, with a left join, returning null value on the "compressed" rows.
a update using a subquery can then "fetch" the values from the previous non-compressed row.

final step: just return the data from that temp table.
0
 
rob_farleyCommented:
Angel,

According to the question, the data is changed on a by-minute basis - but when things don't change, the data is ignored (or as the Asker puts it, 'compressed').

So my query looks back from the @StartTime to see what it was last set to, and forward from the @EndTime to see if it was set to something else at the end of the period.

The second part of mine may not be required, leaving it as:

select
      case
            when Timestamp < @StartTime then @StartTime
            else Timestamp
      end as Timestamp, Value
from
      (
      select *
      from TableInputs
      where Timestamp >= @StartTime and TimeStamp < @EndTime
      and Tag = 'ABC'

      union all
      select * from (
            select top (1) * from TableInputs where Tag = 'ABC' and Timestamp < @StartTime order by Timestamp desc
      ) t
) u
order by Timestamp;

But I don't think he needs a calendar table - because the data can change at any time (it just doesn't get recorded if it hasn't changed).

Rob
0
 
rwallacejAuthor Commented:
thanks to all for your contributions, very much appreciated.
0
 
rwallacejAuthor Commented:
this seems to work, I'll try it out and report back any problems once charting has been tried in full

regards and best wishes,

rwallacej
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now