Solved

SQL querying data that has been compressed

Posted on 2009-07-13
7
311 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 425 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 75 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query that uses count(*) over, possible to use distinct? 18 75
Oracle Date 6 33
Errror when importing data from Oracle to SQL 6 60
SQL: splitting Columns 6 31
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

742 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