Join tables, Date in between check

[anweinbe]

Good afternoon SAS friends. I could use a little help.

I have one table: Temp.a, that has an Employee ID number and a Hire Date. I have another file Temp.B that has an ID number and a Beginning Date and an End Date as well as some other information. Our company recycles ID's. 

I would like to join the files together on Employee ID, but add a clause where the Hire Date must be between the beginning date and end date. That will ensure it matches to the right record in the second table. In the example below, I would want my table 1 to match to the 2nd record of table 2, not the first. 

Table 1:    123456     10/23/2019

Table 2:    123456      01/01/2010        12/31/2018

Table 2:     123456      01/01/2019        12/31/2019

I can write the typical join statement... but I figure I need to add something to this...

where a.empno=b.empno;

THANK YOU!!!!

[Reeza · Accepted solution]

where a.empno=b.empno and a.date between b.BeginningDate and b.EndDate

Something like that? it includes the endpoints of your intervals though, if you don't want to include the dates in the interval use greater than and less than calculations.

---

@anweinbe wrote:

Good afternoon SAS friends. I could use a little help.

I have one table: Temp.a, that has an Employee ID number and a Hire Date. I have another file Temp.B that has an ID number and a Beginning Date and an End Date as well as some other information. Our company recycles ID's. 

I would like to join the files together on Employee ID, but add a clause where the Hire Date must be between the beginning date and end date. That will ensure it matches to the right record in the second table. In the example below, I would want my table 1 to match to the 2nd record of table 2, not the first. 

Table 1:    123456     10/23/2019

Table 2:    123456      01/01/2010        12/31/2018

Table 2:     123456      01/01/2019        12/31/2019

I can write the typical join statement... but I figure I need to add something to this...

where a.empno=b.empno;

THANK YOU!!!!

---

[anweinbe]

Thank you! I knew it was easy.