Left Join equals ID and between two dates

mrmo

New Member
Joined
May 9, 2009
Messages
16
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

<tbody>
</tbody>



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

<tbody>
</tbody>



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

<tbody>
</tbody>





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:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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)
 
Upvote 0
I found the answer. Am I allowed to answer my own question?
Certainly. We actually encourage (and appreciate) it when you do!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top