I'm trying figure out how to return Start Date and End date based on data like in the below table:

The result I am after would show like this:

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

CodePudding user response：

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.

CodePudding user response：

with extended as (
    select dtto, case when datepart(weekday, dtfrom) = 5 then dateadd (day, 2, dtfrom) else from end as dtfrom
    from data
), adjacent as (
    select *,
        case when dateadd(day, 1
            lag(dtfrom) over (partition by name order by dtto)) = dtto then 0 else 1 end as brk
    from extended
), blocked as (
    select *, sum(brk) over (partition by name order by dtto) as grp
    from adjacent
)
select name, min(dtto), max(dtfrom) from grouped
group by name, grp

This will be close. Some of the specifics in your needs will be easy to incorporate. I'm assuming that ranges do no overlap.

CodePudding user response：

I'd make 2 select statments. 1. one for the "Date From" and 2. for the "Date To" value. It would look like this:

SELECT * FROM table_name ORDER "Date From" ASC Limit 1;

SELECT * FROM table_name ORDER "Date From" DESC Limit 1;

Limit gives you only the first value and with order you can select which date you want to have, first or last one.