Modify Query to Allow Drift in Timestamp

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

LVL 1
wint100Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wint100Connect With a Mentor Author Commented:
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
 
FemSteenkampCommented:
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
 
rajeevnandanmishraConnect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
wint100Author Commented:
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
 
wint100Author Commented:
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
 
FemSteenkampConnect With a Mentor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
wint100Author Commented:
Thanks Mark
0
 
wint100Author Commented:
Problem Solved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.