Merge effective dated records of an attribute with the main effective dated table (SQL)

Question

I have a table suppose employee , at the grain if emp_id, dept, start_date, end_date means it maintains the history of the emp_id and dept_id combination .

Emp_id	dept	start_date	end_date
1	10	01/01/2023	03/01/2023
1	10	04/01/2023	12/31/2023

Now I have another table which maintains history of dept along with its attributes

dept	location	start_date	end_date
10	abc	2019-06-18	2020-03-04
10	abc	2021-08-26	2021-08-26
10	def	2021-08-26	2021-08-26

Now how can I expand the employee table to have the history per employee per department merged into it from the dept table.

I have done this for tables where there is just one primary key as in example I have different information of employee in different tables and could merge the dates from each of the tables to make one big table joining on emp_id and merging the dates

But I am not sure how to go about this problem. Any help with examples would be appreciated.

Answer 1

First of all, notice that, while named the same, have different roles and come from different domains (1/1/2023 might never be applicable to department start_dates), so i suggest to call these datapoints distinctly to avoid confustion: emp_start_date, emp_end_date for employee and dept_start_date/dep_end_date for department.

If I assume that you want to "expand" employee grain by adding department grain values valid within the validity period of the employee record, you need to match (join) by the "base" grain + temporal overlap (or, "flipping this over", you need to discard any department validity segments starting AFTER employee validity end or ENDING prior to employee validity, flip this over again for a "good" condition)

  emp_hist join dept_hist on
         dept_hist.dept = emp_hist.dept and
         dept_hist.start_date =< emp_hist.end_date and
         dept_hist.end_date >= emp_hist.start_date