Solved

Modify Query to Allow Drift in Timestamp

Posted on 2011-03-01
9
425 Views
Last Modified: 2012-05-11
I use the query below to return data from a datalogger table, logging values every 15mins. The query should join the columns based on TLInstance and return in Rows where the Timestamp is the same.

The problem I have is sometimes the Timestamp can be 1-2secs different, so I need to add a tolerance into the query to allow for this slight difference.

Can anyone help out in adding this tolerance. I'm thinking it may be better to ignore the timestamp from all other Columns, and only return Timestamp from A_Value, and assume all other data will fit into that Timestamp.
select * from
(
  SELECT [Timestamp], data 
         , case when tlinstance = @TLInstance1 then 'A_Value' 
                when tlinstance = @TLInstance2 then 'B_Value'
                when tlinstance = @TLInstance3 then 'C_Value'
                when tlinstance = @TLInstance4 then 'D_Value'
                when tlinstance = @TLInstance5 then 'E_Value'
                else 'F_Value' end as Value_Code
  FROM TLData
  WHERE TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
  UNION ALL
  SELECT [Timestamp], tl.name 
         , case when tl.tlinstance = @TLInstance1 then 'A_Name' 
                when tl.tlinstance = @TLInstance2 then 'B_Name'
                when tl.tlinstance = @TLInstance3 then 'C_Name'
                when tl.tlinstance = @TLInstance4 then 'D_Name'
                when tl.tlinstance = @TLInstance5 then 'E_Name'
                else 'F_Name' end as Value_Code
  FROM TLData
  INNER JOIN TL on TLDATA.TLINSTANCE = TL.TLINSTANCE
  WHERE tldata.TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)

) srce
PIVOT
( max(data) for Value_Code in ([A_Value],[A_Name],[B_Value],[B_Name],[C_Value],[C_Name],[D_Value],[D_Name],[E_Value],[E_Name],[F_Value],[F_Name])) pvt

Open in new window

0
Comment
Question by:wint100
[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
9 Comments
 
LVL 7

Expert Comment

by:FemSteenkamp
ID: 35007357
change
INNER JOIN TL on TLDATA.TLINSTANCE = TL.TLINSTANCE


to
INNER JOIN TL on TLDATA.TLINSTANCE = TL.TLINSTANCE
AND (TL.Timestamp between DateADD(s,-2,TLD.timestamp) AND TL.Timestamp between DateADD(s,2,TLD.timestamp) )

syntax might need a bit of cleaning up as i am not in front of the sql manager
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 250 total points
ID: 35007514
Hi,

It seems that you need to have a range of values and then compare the data against that range.
In the below code, I have created a temporary table to store the range and then used it in the main query.
I have used the datetime columns (you may need to adjust as per the value in TLInstance column. Give it a try and let us know.

 
declare @Tolerance smallint
set @Tolerance = 5 -- means 5 seconds.
create table #TLInstance (TLInstance datetime, min_TLInstance datetime, max_TLInstance datetime)
insert into #TLInstance 
SELECT @TLInstance1 UNION  SELECT @TLInstance2 UNION  SELECT @TLInstance3 UNION  SELECT @TLInstance4 
UNION  SELECT @TLInstance5 UNION  SELECT @TLInstance6
update #TLInstance set min_TLInstance = dateadd(s,-@Tolerance,TLInstance), max_TLInstance = dateadd(s,@Tolerance,TLInstance)  





select * from
(
  SELECT [Timestamp], a.data 
         , case when b.tlinstance = @TLInstance1 then 'A_Value' 
                when b.tlinstance = @TLInstance2 then 'B_Value'
                when b.tlinstance = @TLInstance3 then 'C_Value'
                when b.tlinstance = @TLInstance4 then 'D_Value'
                when b.tlinstance = @TLInstance5 then 'E_Value'
                else 'F_Value' end as Value_Code
  FROM TLData a, #TLInstance b 
  WHERE (a.tlinstance between b.min_tlinstance and b.max_tlinstance ) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
  UNION ALL
  SELECT [Timestamp], b.name 
         , case when b.tlinstance = @TLInstance1 then 'A_Name' 
                when b.tlinstance = @TLInstance2 then 'B_Name'
                when b.tlinstance = @TLInstance3 then 'C_Name'
                when b.tlinstance = @TLInstance4 then 'D_Name'
                when b.tlinstance = @TLInstance5 then 'E_Name'
                else 'F_Name' end as Value_Code
  FROM TLData a, TL b, #TLInstance c 
  WHERE  (a.tlinstance between c.min_tlinstance and b.max_tlinstance )
	AND b.TLINSTANCE = c.TLINSTANCE 
  and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)

) srce
PIVOT
( max(data) for Value_Code in ([A_Value],[A_Name],[B_Value],[B_Name],[C_Value],[C_Name],[D_Value],[D_Name],[E_Value],[E_Name],[F_Value],[F_Name])) pvt

Open in new window

0
 
LVL 1

Author Comment

by:wint100
ID: 35007754
TLInstance is an Int type, the Timestamp holds the datetime data.

The following flags a Syntax error in the between:
AND (TL.Timestamp between DateADD(s,-2,TLD.timestamp) AND TL.Timestamp between DateADD(s,2,TLD.timestamp) )

The New query above also gives a Syntax error. The TLInstance is an int to get the correct data from the LTData table. It is the Timestamp that needs the tolerance.
0
Technology Partners: 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 1

Author Comment

by:wint100
ID: 35007858
Or could we remove the seconds from Timestamp and only return to the nearest minute. I remove the secs in the SSRS report anyway, so they aren't even needed.
0
 
LVL 1

Accepted Solution

by:
wint100 earned 0 total points
ID: 35008039
This seems to work well:


select * from
(
  SELECT (substring(convert(varchar, Timestamp, 13),0, 18)) as timestamp, data 
         , case when tlinstance = @TLInstance1 then 'A_Value' 
                when tlinstance = @TLInstance2 then 'B_Value'
                when tlinstance = @TLInstance3 then 'C_Value'
                when tlinstance = @TLInstance4 then 'D_Value'
                when tlinstance = @TLInstance5 then 'E_Value'
                else 'F_Value' end as Value_Code
  FROM TLData
  WHERE TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (timestamp>@SDate and Timestamp < @EDate)
  UNION ALL
  SELECT (substring(convert(varchar, Timestamp, 13),0, 18)) as timestamp, tl.name 
         , case when tl.tlinstance = @TLInstance1 then 'A_Name' 
                when tl.tlinstance = @TLInstance2 then 'B_Name'
                when tl.tlinstance = @TLInstance3 then 'C_Name'
                when tl.tlinstance = @TLInstance4 then 'D_Name'
                when tl.tlinstance = @TLInstance5 then 'E_Name'
                else 'F_Name' end as Value_Code
  FROM TLData
  INNER JOIN TL on TLDATA.TLINSTANCE = TL.TLINSTANCE
  
  WHERE tldata.TLInstance in (@TLInstance1,@TLInstance2,@TLInstance3,@TLInstance4,@TLInstance5,@TLInstance6) and isnumeric(data)=1 and Type=0 and (Timestamp>@SDate and Timestamp < @EDate)

) srce
PIVOT
( max(data) for Value_Code in ([A_Value],[A_Name],[B_Value],[B_Name],[C_Value],[C_Name],[D_Value],[D_Name],[E_Value],[E_Name],[F_Value],[F_Name])) pvt

Open in new window

0
 
LVL 7

Assisted Solution

by:FemSteenkamp
FemSteenkamp earned 250 total points
ID: 35008259
sorry my bad

try this ( copy paste error)

INNER JOIN TL on TLDATA.TLINSTANCE = TL.TLINSTANCE
AND (TL.Timestamp between DateADD(s,-2,TLD.timestamp) AND DateADD(s,2,TLD.timestamp) )

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35012953
Rather than : (substring(convert(varchar, Timestamp, 13),0, 18)) as timestamp

Best to use date functions : dateadd(minute,datediff(minute,0,timestamp),0) as timestamp

Then you can still sort properly because it is retained as a datetime.

Alternatively, if you want the data grouped into 5 second blocks then you could use a calendar table
0
 
LVL 1

Author Comment

by:wint100
ID: 35014374
Thanks Mark
0
 
LVL 1

Author Closing Comment

by:wint100
ID: 35045512
Problem Solved
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

687 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