Solved

Modify Query to Allow Drift in Timestamp

Posted on 2011-03-01
9
420 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
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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

929 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

9 Experts available now in Live!

Get 1:1 Help Now