I have a table of several date ranges for an employee in a department. I'm trying to create a view that only returns 1 row for each employee and department and with no overlapping dateranges between the employees departments. I want to alter some ranges, if they overlap with others.
The 'Date from' should return the minimum date for each of the employees departments. The 'Date to' should return the maximum date, but the maximum date must be less than the minimum date of the next department, ordered by 'Date from'.
Any ideas on how to get the result I'm looking for?
Sample of what I have:
| Date from | Date to | Emp ID | Dep ID |
|---|---|---|---|
| 01-02-2019 | 31-12-2019 | 1 | 7 |
| 25-02-2020 | 31-01-2021 | 1 | 7 |
| 15-02-2021 | 09-05-2021 | 1 | 7 |
| 01-01-2020 | 31-12-9999 | 1 | 7 |
| 01-02-2021 | 14-02-2021 | 1 | 8 |
| 26-04-2021 | 31-12-9999 | 1 | 8 |
What I want:
| Date from | Date to | Emp ID | Dep ID |
|---|---|---|---|
| 01-02-2019 | 31-01-2021 | 1 | 7 |
| 01-02-2021 | 31-12-9999 | 1 | 8 |
I havent gotten any further than returning the minimum date for each employee/department
SELECT MIN([Date from]) AS [Date from]
, [Emp ID]
, [Dep ID]
FROM Table
WHERE [Date to] <= GETDATE()
GROUP BY [Emp ID]
, [Dep ID]
| Date from | Emp ID | Dep ID |
|---|---|---|
| 01-02-2019 | 1 | 7 |
| 01-02-2021 | 1 | 8 |
Use https://dbfiddle.uk/wwSSRmBW for added example data for this kind of questions. My solution as below:
with cte as
(select min(date_from) over(partition by emp_id,dep_id) mndate,max(date_to) over(partition by emp_id,dep_id) mxdate,*,dense_rank() over(partition by emp_id order by dep_id) rn from temp)
, cte1
as
(
select *,isnull(f.mndate2,c1.mxdate) ddate from cte c1 outer apply (select min(date_from) mndate2 from cte where c1.rn+1=rn and emp_id=c1.emp_id ) as f
)
select min(mndate),max(date_to),emp_id,dep_id from cte1 where date_to<=ddate
group by emp_id,dep_id