Solved

Modify Query to Allow Drift in Timestamp

Posted on 2011-03-01
9
419 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
9 Comments
 
LVL 7

Expert Comment

by:FemSteenkamp
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:wint100
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Accepted Solution

by:
wint100 earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Mark
0
 
LVL 1

Author Closing Comment

by:wint100
Comment Utility
Problem Solved
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 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

17 Experts available now in Live!

Get 1:1 Help Now