Find substantially simultaneously activities from different tables

Transact-SQL
1

I've two different tables and try to find substantially simultaneously activities (eg. +/- 5 minutes).

Assume:

T1
--
id, activity, act_time
1, HG, 2015-05-02 12:03:32
2, ZK, 2015-05-12 02:13:22


T2
--
id, activity, act_time
1, R-22-BK, 2015-05-02 12:04:12
2, B-234-KK, 2015-05-12 12:13:22
3, T-44-KX, 2015-05-02 12:02:22

Find all activities in t1 which took place almost simultaneously (eg. plus or minus 5 minutes) with activities in t2.

1, HG, 2015-05-02 12:03:32

  • matches -
    1, R-22-BK, 2015-05-02 12:04:12
    3, T-44-KX, 2015-05-02 12:02:22

I hope it is sufficiently clear.

Every assistance is appreciated.

2 
SELECT T1.*, T2.*
FROM 
	T1
	INNER JOIN T2 ON
		ABS(DATEDIFF(mi,T1.act_time, T2.Act_time)) <= 5
WHERE
	t1.ID = 1;
1 Like
3

This works excellent ! Many thanks !

The next step is to group the results on the T2.activity column and count the occurrences of the activities (which activity is a most common match).

When i try this (GROUP BY T2.activity) I receive an error because not all other columns are in the select list (because it is not contained in either an aggregate function or the GROUP BY clause)

Is it possible to group on T2.activity without adding all other columns.

I think this a difficult part of sql server. MySQL is less strict :smile: