Solved

SQL querying data that has been compressed

Posted on 2009-07-13
7
271 Views
Last Modified: 2012-05-07
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
Comment
Question by:rwallacej
7 Comments
 
LVL 14

Accepted Solution

by:
rob_farley earned 425 total points
Comment Utility
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
 
LVL 7

Assisted Solution

by:Mohed Sharfi
Mohed Sharfi earned 75 total points
Comment Utility
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
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
mSharfi,

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

Rob
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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
 

Author Comment

by:rwallacej
Comment Utility
thanks to all for your contributions, very much appreciated.
0
 

Author Closing Comment

by:rwallacej
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now