By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table #test
(
start_date date,
end_date date,
emp_id varchar(20),
seating_type varchar(10)
)
insert into #test
select '01-01-2025','01-10-2025','123','abc'
union
select '01-11-2025','01-20-2025','123','abc'
union
select '01-21-2025','01-31-2025','123','def'
union
select '02-01-2025','02-10-2025','123','abc'
union
select '02-11-2025','02-20-2025','123','def'
union
select '02-21-2025','02-28-2025','123','gih'
union
select '03-01-2025','03-10-2025','123','def'
SELECT * FROM #test
| start_date | end_date | emp_id | seating_type |
|---|---|---|---|
| 2025-01-01 | 2025-01-10 | 123 | abc |
| 2025-01-11 | 2025-01-20 | 123 | abc |
| 2025-01-21 | 2025-01-31 | 123 | def |
| 2025-02-01 | 2025-02-10 | 123 | abc |
| 2025-02-11 | 2025-02-20 | 123 | def |
| 2025-02-21 | 2025-02-28 | 123 | gih |
| 2025-03-01 | 2025-03-10 | 123 | def |
--The heart of the solution requires the calculation of 2 types of row number partitioning
SELECT *,
ROW_NUMBER() OVER (ORDER BY start_date) rn,
ROW_NUMBER() OVER (PARTITION BY emp_id,Seating_Type ORDER BY start_date) rn2,
ROW_NUMBER() OVER (ORDER BY start_date) - ROW_NUMBER() OVER (PARTITION BY emp_id,Seating_Type ORDER BY start_date) [rn-rn2 grp]
FROM #test
| start_date | end_date | emp_id | seating_type | rn | rn2 | rn-rn2 grp |
|---|---|---|---|---|---|---|
| 2025-01-01 | 2025-01-10 | 123 | abc | 1 | 1 | 0 |
| 2025-01-11 | 2025-01-20 | 123 | abc | 2 | 2 | 0 |
| 2025-02-01 | 2025-02-10 | 123 | abc | 4 | 3 | 1 |
| 2025-01-21 | 2025-01-31 | 123 | def | 3 | 1 | 2 |
| 2025-02-11 | 2025-02-20 | 123 | def | 5 | 2 | 3 |
| 2025-03-01 | 2025-03-10 | 123 | def | 7 | 3 | 4 |
| 2025-02-21 | 2025-02-28 | 123 | gih | 6 | 1 | 5 |
--Final soution with debug columns
WITH CTE as
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY start_date) rn,
ROW_NUMBER() OVER (ORDER BY start_date) - ROW_NUMBER() OVER (PARTITION BY emp_id,Seating_Type ORDER BY start_date) grp
FROM #test
)
SELECT Min(start_date) as MinStart_date, max(end_date) as MaxEnd_date, emp_id, seating_type, grp
FROM CTE
GROUP BY grp, emp_id, seating_type
ORDER BY min(rn)
| MinStart_date | MaxEnd_date | emp_id | seating_type | grp |
|---|---|---|---|---|
| 2025-01-01 | 2025-01-20 | 123 | abc | 0 |
| 2025-01-21 | 2025-01-31 | 123 | def | 2 |
| 2025-02-01 | 2025-02-10 | 123 | abc | 1 |
| 2025-02-11 | 2025-02-20 | 123 | def | 3 |
| 2025-02-21 | 2025-02-28 | 123 | gih | 5 |
| 2025-03-01 | 2025-03-10 | 123 | def | 4 |
--Final soution with debug columns removed
WITH CTE as
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY start_date) rn,
ROW_NUMBER() OVER (ORDER BY start_date) - ROW_NUMBER() OVER (PARTITION BY emp_id,Seating_Type ORDER BY start_date) grp
FROM #test
)
SELECT Min(start_date) as Start_date, max(end_date) as End_date, emp_id, seating_type
FROM CTE
GROUP BY grp, emp_id, seating_type
ORDER BY min(rn)
| Start_date | End_date | emp_id | seating_type |
|---|---|---|---|
| 2025-01-01 | 2025-01-20 | 123 | abc |
| 2025-01-21 | 2025-01-31 | 123 | def |
| 2025-02-01 | 2025-02-10 | 123 | abc |
| 2025-02-11 | 2025-02-20 | 123 | def |
| 2025-02-21 | 2025-02-28 | 123 | gih |
| 2025-03-01 | 2025-03-10 | 123 | def |