question

SK-4775 avatar image
0 Votes"
SK-4775 asked EchoLiu-msft commented

joining two tables with timestamps

HI SQL Gurus. I have two tables.
Table A (timestamp, event)
Table B (Timestamp, val1, val2, val3)

I need to join these two tables on time stamp but the catch is that while joining, I have to aggregate the val1,2 and 3 during a widow of +4 second and -4 seconds.

e.g. if the time stamp in A is 12:32:10:0000, so this value upto second is used for the join and the window to join to table B would be 12:32:10 + 4 seconds and 12:32:10-4 seconds. its like joining one value from A to upto 8 values in B and then taking the aggregate of the values in B and displaying them as
12:32:10:0000 | AVG(val1) | AVG(val2) | AVG(val3)

Any help would be appreciated.

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What data type are you using for timestamp columns?

Which window to include from B if A.timestamp is 12:32:09, for example?


0 Votes 0 ·

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

From your verbal description, it sounds like:

SELECT A.timestamp, AVG(B.val1), AVGV(B.val2), AVG(B.val3)
FROM   A
JOIN     B WHERE B.timestamp BETWEEN dateadd(second, -4, A.timestamp) AND dateadd(second, 4, A.timestamp)
GROUP BY A.timestamp

But I have a feeling there are a few things hiding here. What if the timestamps in A are only a second apart? In such case, some values will be counted more than once. Which may or may not matter.

Generally, for this type of question, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with sample data, and then the desired result given the sample. This can help to straighten out ambiguities in the verbal description. And it males it easy to copy and paste into a query window to develop a tested solution. The suggestion above is untested.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @SK-4775

Welcome to the microsoft TSQL Q&A forum!

Maybe what you want is:

 SELECT A.timestamp, AVG(B.val1), AVG(B.val2), AVG(B.val3)
 FROM  A
 JOIN  B 
 ON B.timestamp 
 BETWEEN dateadd(second, -4, A.timestamp) AND dateadd(second, 4, A.timestamp)
 GROUP BY A.timestamp

If this does not solve your problem, please provide more details as ErlandSommarskog suggested.

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.