Solved

SQL querying data that has been compressed

Posted on 2009-07-13
7
299 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Importing Table Query With Specific Timestamp Daily 2 34
How to fix Datetime in MySQL? 4 51
IIF in access query 19 27
Import text from rows into columns? 12 44
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
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…

828 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