Solved

Modify Query to Allow Drift in Timestamp

Posted on 2011-03-01
9
424 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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 Task 11 44
SCOM to SQL port 14xx failed? 1 32
add stored proc on publlication 4 20
MSSQL Convert Char to Date Time 5 13
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

733 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