date timeframe

  • boehnc

    Right there with Babe

    Points: 744

    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;

  • drew.allen

    SSC Guru

    Points: 76458

    What have you tried?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • boehnc

    Right there with Babe

    Points: 744

    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;

  • DesNorton

    SSC-Insane

    Points: 22544

    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

  • boehnc

    Right there with Babe

    Points: 744

    I appreciate your help..this worked great! Thank you.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply