I’m trying figure out how to return Start Date and End date based on data like in the below table:
| Name | Date From | Date To |
|---|---|---|
| A | 2022-01-03 | 2022-01-03 |
| A | 2021-12-29 | 2021-12-31 |
| A | 2021-12-28 | 2021-12-28 |
| A | 2021-12-27 | 2021-12-27 |
| A | 2021-12-23 | 2021-12-24 |
| A | 2021-11-08 | 2021-11-09 |
The result I am after would show like this:
| Name | Date From | Date To |
|---|---|---|
| A | 2021-12-23 | 2022-01-03 |
| A | 2021-11-08 | 2021-11-09 |
The dates in first table will sometimes go over weekends with the Date From and Date To, but in cases where the row ends on a Friday and next row starts on following Monday it will need to be classified as the same “block”, as presented in the second table. I was hoping to use DATEFIRST setting to cater for the weekends to avoid using a calendar table, as per How do I exclude Weekend days in a SQL Server query?, but if calendar table ends up being the easiest way out I’m happy to look into creating one.
In above example I only have 1 Name, but the table will have multiple names and it will need to be grouped by that.
The only examples of this I am seeing are using only 1 date column for records and I struggled changing their code around to cater for my example. The closest example I found doesn’t work for me as it is based on datetime fields and the time differences – find start and stop date for contiguous dates in multiple rows
Advertisement
Answer
This is a Gaps & Island problem with the twist that you need to consider weekend continuity.
You can do:
select max(name) as name, min(date_from) as date_from, max(date_to) as date_to
from (
select *, sum(inc) over(order by date_to) as grp
from (
select *,
case when lag(ext_to) over(order by date_to) = date_from
then 0 else 1 end as inc
from (
select *,
case when (datepart(weekday, date_to) = 6)
then dateadd(day, 3, date_to)
else dateadd(day, 1, date_to) end as ext_to
from t
) x
) y
) z
group by grp
Result:
name date_from date_to ---- ---------- ---------- A 2021-11-08 2021-11-09 A 2021-12-23 2022-01-03
See running example at db<>fiddle #1.
Note: Your question doesn’t mention it, but you probably want to segment per person. I didn’t do it.
EDIT: Adding partition by name
Partitioning by name is quite easy actually. The following query does it:
select name, min(date_from) as date_from, max(date_to) as date_to
from (
select *, sum(inc) over(partition by name order by date_to) as grp
from (
select *,
case when lag(ext_to) over(partition by name order by date_to) = date_from
then 0 else 1 end as inc
from (
select *,
case when (datepart(weekday, date_to) = 6)
then dateadd(day, 3, date_to)
else dateadd(day, 1, date_to) end as ext_to
from t
) x
) y
) z
group by name, grp
order by name, grp
See running query at db<>fiddle #2.