CODEWITHSUNDEEP
sqlsql-serverjointimestamp
Dog
Dog

Reputation: 115

Join two tables based on timestamps with a 5 minute interval from each other

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

 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:

SELECT A.Freezer, A.Timestamp, Temperature_1,Temperature_2 From TABLE_A as A
RIGHT JOIN TABLE_B as B
ON A.FREEZER = B.FREEZER
WHERE A.Timestamp = B.Timestamp (this of course doesn't work because timestamps aren't the same)

Upvotes: 1

Views: 2275

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Does this do what you want?

SELECT . . .
From TABLE_A A JOIN
     TABLE_B B
     ON A.FREEZER = B.FREEZER AND
        A.Timestamp >= DATE_ADD(minute, -5, B.TimeStamp) AND
        A.Timestamp <= DATE_ADD(minute, 5, B.TimeStamp) ;

I see no reason for an outer JOIN.

If you want to find the nearest timestamp, then use apply:

SELECT . . .
FROM table_a a cross apply
     (select top (1) b.*
      from table_b b
      where a.freezer = b.freezer 
      order by abs(datediff(second, a.timestamp, b.timestamp))
     ) b;

You can add a where clause to the subquery to limit the range of matches to five minutes.

Upvotes: 2

DxTx
DxTx

Reputation: 3357

Try this query

SELECT T1.freezer, 
       T1.[timestamp], 
       T1.temperature_1, 
       T2.temperature_2 
FROM   table_1 T1
       INNER JOIN table_2 T2
       ON T1.[timestamp] = Dateadd(minute, -(Cast(Datepart(minute, T2.[timestamp]) AS INT) % 5), T2.[timestamp])

Result

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

Demo: http://www.sqlfiddle.com/#!18/ffa1e/2/0

Upvotes: 1

Related Questions