'How to correctly join two tables that each have Start Date and Stop Date columns?
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
