CODEWITHSUNDEEP
sqlsql-servergroup-bytimestampaverage
Dog
Dog

Reputation: 115

Join tables with dates within intervals of 5 min (get avg)

I want to join two tables based on timestamp, the problem is that both tables didn't had the exact same timestamp so i want to join them using a near timestamp using a 5 minute interval.

This query needs to be done using 2 Common table expressions, each common table expression needs to get the timestamps and group them by AVG so they can match

Freezer |       Timestamp      | Temperature_1
 1      2018-04-25 09:45:00       10  
 1      2018-04-25 09:50:00       11   
 1      2018-04-25 09:55:00       11


Freezer |       Timestamp      | Temperature_2
 1      2018-04-25 09:46:00       15  
 1      2018-04-25 09:52:00       13   
 1      2018-04-25 09:59:00       12

My desired result would be:

Freezer |       Timestamp      | Temperature_1 | Temperature_2
 1      2018-04-25 09:45:00       10             15
 1      2018-04-25 09:50:00       11             13
 1      2018-04-25 09:55:00       11             12

The current query that i'm working on is:

WITH Temperatures_1 (
SELECT Freezer, Temperature_1, Timestamp
FROM TABLE_A
),

WITH Temperatures_2 (
SELECT Freezer, Temperature_2, Timestamp
FROM TABLE_B
)

SELECT A.Freezer, A.Timestamp, Temperature_1, Temperature_2
FROM Temperatures_1 as A
RIGHT JOIN Temperatures_2 as B
ON A.FREEZER = B.FREEZER
WHERE A.Timestamp = B.Timestamp

Upvotes: 0

Views: 1289

Answers (2)

Klajdi Begeja
Klajdi Begeja

Reputation: 31

You should change the key of join two table by adding the timestamp. The timestamp you should need to approximate the datetime on both side tables A and B tables. First you should check if the value of the left table (A) datetime is under 2.5 minutes then approximate to the near 5 min. If it is greater the approximate to the next 5 minutes. The same thing you should do on the right table (B). Or you can do this on the CTE and the right join remains the same as your query.

Upvotes: 0

STLDev
STLDev

Reputation: 6174

You should may want to modify your join criteria instead of filtering the output. Use BETWEEN to bracket your join value on the timestamps. I chose +/- 150 seconds because that's half of 2-1/2 minutes to either side (5-minute range to match). You may need something different.

;WITH Temperatures_1 (
SELECT Freezer, Temperature_1, Timestamp
FROM TABLE_A
),

WITH Temperatures_2 (
SELECT Freezer, Temperature_2, Timestamp
FROM TABLE_B
)

SELECT A.Freezer, A.Timestamp, Temperature_1, Temperature_2
FROM Temperatures_1 as A
RIGHT JOIN Temperatures_2 as B
ON A.FREEZER = B.FREEZER 
    AND A.Timestamp BETWEEN (DATEADD(SECOND, -150, B.Timestamp) 
                            AND (DATEADD(SECOND, 150, B.Timestamp)

Upvotes: 1

Related Questions