This should be easy, maybe even a duplicate, but neither my brain nor my search engine is being very helpful today.
My problem is as below:
Given two tables that both have:
- A start date
- A stop date
- A property of an employee. Let's say Desk Number in table A and Team in table B
- The employee's unique ID number.
join together the two tables in some honest way such that you only need one start and stop date per row in the resulting table.
Example input and desired output: Table A:
Start Date Stop Date ID Desk Number 01/20 05/20 0100 55 03/20 06/20 0100 56 02/22 04/22 0200 91
Table B (notice the overlap in employee 0100's dates):
Start Date Stop Date ID Team Number 01/20 04/20 0100 2 02/20 06/20 0100 3 02/22 04/22 0200 8
Example output:
Start Date Stop Date ID Desk Number Team Number 01/20 04/20 0100 55 2 02/20 05/20 0100 55 3 02/20 06/20 0100 56 3 02/22 04/22 0200 91 8
I can handle manipulating the resulting dates once the tables are joined correctly, but the join in of itself is causing me issues. I just can't figure out how to make sure that some dates that fall outside of the range don't slip in. My currently solution for the join, which I'm unsure of, was to just join on
[Table A].[Start Date] <= [Table B].[Stop Date]
[Table B].[Start Date] <= [Table A].[Stop Date]
and then take the max/min date as appropriate (don't worry about that part, I only care about the join), but I very much doubt that the solution could really be that simple.
CodePudding user response：
Assuming I'm not getting something wrong here, the desired output should be
Start Date Stop Date ID Desk Number Team Number 01/20 04/20 0100 55 2 02/20 05/20 0100 55 3 03/20 04/20 0100 56 2 03/20 06/20 0100 56 3 02/22 04/22 0200 91 8
Your intuition as to the join is indeed correct, this produces the right result:
SELECT CASE
WHEN teams.start_date > desks.start_date
THEN teams.start_date
ELSE desks.start_date
END start_date
, CASE
WHEN teams.stop_date < desks.stop_date
THEN teams.stop_date
ELSE desks.stop_date
END stop_date
, desks.id
, desks.desk_number
, teams.team_number
FROM desks
JOIN teams
ON desks.start_date < teams.stop_date
AND teams.start_date < desks.stop_date
AND teams.id = desks.id