I have HR dataset problem. For a position, I have position details such as category, type, etc. These details change over time and independently of each other, resulting in the following datasets:
Position StartDate EndDate
1 '2020-01-01' '3000-01-01'
Position Type StartDate EndDate
1 'Part-time' '2020-01-01' '2020-07-31'
1 'Full-time' '2020-08-01' '2020-08-30'
1 'Part-time' '2020-09-01' '3000-01-01
Position Category StartDate EndDate
1 'Agency' '2020-01-01' '2020-06-30'
1 'Employee' '2020-07-01' '2020-08-30'
1 'Contractor' '2020-09-01' '3000-01-01
So from this, I would like to get a dataset such as:
Position Type Category StartDate EndDate
1 'Part-time' 'Agency' '2020-01-01' '2020-06-30'
1 'Part-time' 'Employee' '2020-07-01' '2020-07-31'
1 'Full-time' 'Employee' '2020-08-01' '2020-08-30'
1 'Part-time' 'Contractor' '2020-09-01' '3000-01-01'
So that when filtered on a date, it will bring back the situation for that position on that date. For this example on '2020-07-05', the position will show as a part-time employee.
Methods I've looked at so far include CASE statements for creating the StartDate and EndDate fields
And creating a union of the datasets, and running ROW_NUMBER() OVER(PARTITION BY Position OVER BY StartDate, EndDate) to try to filter out the wrong rows when these tables are joined.
Edit: Apologies, I should have added, this is the minimum example of the problem. The example I'm looking at will have 6-12 columns. So the solution will need to be scalable.
The method I'd choose is to apply a CROSS JOIN between the Types and the Categories table with filtering by overlapping phases of both tables.
In the SELECT list you can apply your CASE Statement to choose the right date for your date range.