Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL querying data that has been compressed

Posted on 2009-07-13
7
Medium Priority
?
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Accepted Solution

by:
rob_farley earned 1700 total points
ID: 24846863
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 300 total points
ID: 24846906
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 15

Expert Comment

by:rob_farley
ID: 24846945
mSharfi,

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

Rob
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24846975
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 15

Expert Comment

by:rob_farley
ID: 24847024
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
ID: 24847152
thanks to all for your contributions, very much appreciated.
0
 

Author Closing Comment

by:rwallacej
ID: 31603168
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

705 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