Get Start and End date from multiple rows of dates, excluding weekends

Question

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

I don't understand the results. Why is `2021-12-24` "continuous" with `2021-12-27` when they are 3 days apart? — Thom A, Nov 02 '21 at 14:08
@Larnu apologies, I forgot to say it needs to ignore weekends. — Pawel, Nov 02 '21 at 14:09
@Larnu would it not be possible with `DATEFIRST` setting on SQL Server? I am trying to avoid using a calendar table. — Pawel, Nov 02 '21 at 14:13
I only said I was hoping to, but if like you are suggesting that without it its too complicated or rather long-winded then I am willing to look at creating one. — Pawel, Nov 02 '21 at 14:23

The Impaler · Accepted Answer · 2021-11-02T14:39:20.780

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.

Yeah, apologies... I ended up re-writing the question as per the first comments under it and lost that detail... as in the results being grouped by Name. I will see if I can play around with your query. — Pawel, Nov 02 '21 at 14:36

shawnt00 · Answer 2 · 2021-11-03T18:17:05.513

with extended as (
    select name,
        date_from,
        case when datepart(weekday, date_to) = 6
            then dateadd(day, 2, date_to) else date_to end as date_to
    from t
), adjacent as (
    select *,
        case when dateadd(day, 1,
            lag(date_to) over (partition by name order by date_from)) = date_from
                then 0 else 1 end as brk
    from extended
), blocked as (
    select *, sum(brk) over (partition by name order by date_from) as grp
    from adjacent
)
select name, min(date_from), max(date_to) from blocked
group by name, grp;

I'm assuming that ranges do no overlap and that all input dates do fall on weekdays. While hammering this out on my cellphone I originally made two mistakes. For some reason I got to and from dates reversed in my head and then I was thinking that Friday is 5 (as with @@datefirst) rather than 6. (Of course this could otherwise vary with the regional setting anyway.) One advantage of using table expressions is to modularize and bury certain details in lower levels of the logic. In this case it would be very easy to adjust dates should some of these assumptions prove to be wrong.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=42e0c452d57d474232bcf991d6d3c43c

Get Start and End date from multiple rows of dates, excluding weekends

2 Answers2