Results 1 to 3 of 3

Thread: Left Join equals ID and between two dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Left Join equals ID and between two dates

    Hi all,


    Hoping someone can help me with query I'm having trouble with. I want to do a left join and keep all records in Table 1, and matching records in Table 2 where Table 1 ID = Table 2 ID, AND Table 1 RowDate is between Table 2 StartDate and EndDate.


    Here is a sample table set up below:


    Table 1:
    EmployeeID EmployeeName RowDate Metric
    1111 John Johnson 01/01/2019 125
    1111 John Johnson 01/02/2019 105
    1111 John Johnson 01/03/2019 180
    1111 John Johnson 01/04/2019 111
    2222 Mike Montgomery 01/01/2019 255
    2222 Mike Montgomery 01/02/2019 212
    2222 Mike Montgomery 01/03/2019 280
    2222 Mike Montgomery 01/04/2019 222
    3333 Sarah Sikes 01/01/2019 358
    3333 Sarah Sikes 01/02/2019 356
    3333 Sarah Sikes 01/03/2019 324
    3333 Sarah Sikes 01/04/2019 385
    4444 Willie Wilson 01/01/2019 435
    4444 Willie Wilson 01/02/2019 483
    4444 Willie Wilson 01/03/2019 412
    4444 Willie Wilson 01/04/2019 483



    Table 2:
    EmployeeID Supervisor StartDate EndDate
    1111 Sup 1 01/08/2017 11/19/2017
    1111 Sup 2 11/20/2017 12/01/2018
    1111 Sup 3 12/02/2018 12/31/9999
    2222 Sup 4 01/12/2018 12/05/2018
    2222 Sup 5 12/06/2018 12/31/2018
    3333 Sup 6 01/01/2015 12/31/9999



    Expected Query Result:
    EmployeeID EmployeeName RowDate Metric Supervisor
    1111 John Johnson 01/01/2019 125 Sup 3
    1111 John Johnson 01/02/2019 105 Sup 3
    1111 John Johnson 01/03/2019 180 Sup 3
    1111 John Johnson 01/04/2019 111 Sup 3
    2222 Mike Montgomery 01/01/2019 255
    2222 Mike Montgomery 01/02/2019 212
    2222 Mike Montgomery 01/03/2019 280
    2222 Mike Montgomery 01/04/2019 222
    3333 Sarah Sikes 01/01/2019 358 Sup 6
    3333 Sarah Sikes 01/02/2019 356 Sup 6
    3333 Sarah Sikes 01/03/2019 324 Sup 6
    3333 Sarah Sikes 01/04/2019 385 Sup 6
    4444 Willie Wilson 01/01/2019 435
    4444 Willie Wilson 01/02/2019 483
    4444 Willie Wilson 01/03/2019 412
    4444 Willie Wilson 01/04/2019 483





    In the expected query result, I want all rows from Table 1. Then the Supervisor value can be null when it can't find the EmployeeID at all, or when it can't find the RowDate between Start and End dates for an employee in Table 2.


    Thanks for looking!
    Last edited by mrmo; Apr 18th, 2019 at 07:15 PM.

  2. #2
    New Member
    Join Date
    May 2009
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Left Join equals ID and between two dates

    I found the answer. Am I allowed to answer my own question? Oh well, if so, here's the answer.

    SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Query1.Supervisor
    FROM Table1 LEFT JOIN (SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Table2.Supervisor FROM Table1 INNER JOIN Table2 ON Table1.EmployeeID = Table2.EmployeeID WHERE (((Table2.StartDate)<=[RowDate]) AND ((Table2.EndDate)>=[RowDate]))) AS Query1 ON (Table1.EmployeeID = Query1.EmployeeID) AND (Table1.RowDate = Query1.RowDate)

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Left Join equals ID and between two dates

    I found the answer. Am I allowed to answer my own question?
    Certainly. We actually encourage (and appreciate) it when you do!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •