I have two tables one for Employee’s Name P_NAMES this table contains a historical record of employees if they change their names since they were hired “Effective Date” some have one row some have 4 or 6 rows depending how many times they changed their name.

I have another table for Employee History that contains their job code their supervisor and their title etc. P_HIST

I want to show the employee history with the actual name at that time “Effective Date”. Not sure if I should create a new table or just a simple join. Ideally I can get this data by using Effective date between but that would have to be a manual query.

Here is an example

SELECT 
    a.[EMPLID],
    a.[EFFDT],
    a.[NAME]
FROM
    [P_NAMES] a

enter image description here

Here is the Employee History

SELECT 
    b.EMPLID,
    b.NAME,
    b.EFFDT
FROM 
    [P_HIST] b

enter image description here

I could get this data if I write a simple query like this

Select .
.
.
where
.
and EFFDT   between 
  -- '2008-08-04 00:00:00.000' and '2011-12-01 00:00:00.000'
  --'2011-12-02 00:00:00.000' and '2017-04-05 00:00:00.000'
   '2017-04-06 00:00:00.000' and '2999-12-31 00:00:00.000'

the end result would look like this

enter image description here

Any ideas?

I tried this, for each date

SELECT 
    a.[EMPLID],
    a.[EFFDT],
    b.[EFFDT],
    b.[NAME]
FROM 
    [P_NAMES] a, [P_HIST] b
WHERE  
    a.[EMPLID] = b.[EMPLID]
    AND b.EFFDT BETWEEN '2017-04-06 00:00:00.000' AND '2999-12-31 00:00:00.000'

enter image description here