October 11, 2018 at 12:01 pm
drawing a blank;
have one table (Table1) that needs to look in another table (Table2) and for every UserID/Date row bring back from Table 2 any record where the date is +/- 3 days from the date in Table 1.
thanks for any assistance.
UserID(Table1)/Date1(Table1)/Date2(Table2)
UserID Date1 Date2
1000 10/10/2018 10/09/2018
1000 10/12/2018 10/09/2018
1100 09/09/2018 09/10/2018
1100 09/12/2018 09/10/2018
1100 09/14/2018
For simplicity:
SELECT UserID, Date
FROM Table1;
SELECT UserID, Date
FROM Table 2;
October 12, 2018 at 12:05 pm
What have you tried?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2018 at 1:00 pm
tried starting out with the SQL below, but it's not evaluating each UserID/Row...only bringing back the "ordered" date in T2 obviously.
select t1.*,
(select t2.Date
from t2
where t2.userid = t1.userid
order by (t2.Date - t1.date) asc
offset 0 rows
fetch next 1 rows only
) as t2_date
from t1;
October 12, 2018 at 1:04 pm
Based on the limited information that you have provided, I would try the following code.
Note, this is untested code, as I have nothing to test it against. The code also assumes that the [date] fields are of type datetime.SELECT t1.UserID, Date1 = t1.Date, Date2 = t2.Date
FROM Table1 AS t1
LEFT JOIN Table2; AS t2
ON t1.UserID = t2.UserID
AND t1.Date >= t2.Date -3
AND t1.Date <= t2.Date +3
October 19, 2018 at 8:35 am
I appreciate your help..this worked great! Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply