Group islands of contiguous dates, including missing weekends

user_13672434 0
Viewed 115

I have a large data set with dates for certain actions, and I am trying count the consecutive dates. Searching around I found this: https://www.sqlservercentral.com/articles/group-islands-of-contiguous-dates-sql-spackle and it is near perfect, it is doing exactly what I’m looking for. Unfortunately due to my data set, I have one exception business rule I would need the query to do: if the employees last date is Friday, and the next start date is the nearest Monday, it should group those dates to the same “island” without increasing the day count. Here’s what I mean with an example dataset:

CREATE TABLE Actions    
   ([Employee] varchar(2), [ActionDate] date)    
;       

INSERT INTO Actions    
    ([Employee], [ActionDate])

VALUES    
    ('AA', '2019-01-03'),    
    ('AA', '2019-01-04'),    
    ('AA', '2019-01-07'),    
    ('AA', '2019-01-08'),    
    ('BB', '2019-08-01'),    
    ('BB', '2019-08-02'),    
    ('BB', '2019-08-03'),    
    ('BB', '2019-08-04'),    
    ('BB', '2019-08-05'),    
    ('BB', '2019-08-06'),    
    ('CC', '2019-09-09'),    
    ('CC', '2019-09-10'),    
    ('CC', '2019-09-11'),    
    ('CC', '2019-09-12'),    
    ('CC', '2019-09-13'),    
    ('CC', '2019-09-16'),    
    ('CC', '2019-09-17'),    
    ('CC', '2019-09-18')    
;

And the Query that I found, changed the columns to match the example:

WITH    
days As    
(    
SELECT Employee,    
       ActionDate,    
       DATEADD(dd, -ROW_NUMBER() OVER  (PARTITION BY Employee ORDER BY Employee, ActionDate), ActionDate) As grouping    
FROM Actions    
GROUP BY Employee, ActionDate    
)    
SELECT Employee,    
       MIN(ActionDate) AS ActionStart,    
       MAX(ActionDate) As ActionEnd,    
       DATEDIFF(dd,MIN(ActionDate),MAX(ActionDate))+1 As ActLength    
FROM days    
GROUP BY Employee, grouping    
ORDER BY Employee, ActionStart

Results are:

+-------+----------+-------------+------------+-----------+
| RowNr | Employee | ActionStart | ActionEnd  | ActLength |
+-------+----------+-------------+------------+-----------+
|     1 | AA       |  03.01.2019 | 04.01.2019 |         2 |
|     2 | AA       |  07.01.2019 | 08.01.2019 |         2 |
|     3 | BB       |  01.08.2019 | 06.08.2019 |         6 |
|     4 | CC       |  09.09.2019 | 13.09.2019 |         5 |
|     5 | CC       |  16.09.2019 | 18.09.2019 |         3 |
+-------+----------+-------------+------------+-----------+

In this example, employee AA has end date 4.1.2019 Friday and 7.1.2019 start date is the nearest Monday. CC also has one with end date on a Friday 13.9.2019 and the next start date is the nearest Monday, 16.9.2019. It should “combine” these dates without increasing the ActLength. So the desired results would be:

+-------+----------+-------------+------------+-----------+
| RowNr | Employee | ActionStart | ActionEnd  | ActLength |
+-------+----------+-------------+------------+-----------+
|     1 | AA       |  03.01.2019 | 08.01.2019 |         4 |
|     2 | BB       |  01.08.2019 | 06.08.2019 |         6 |
|     3 | CC       |  09.09.2019 | 18.09.2019 |         8 |
+-------+----------+-------------+------------+-----------+

Does anyone know is it possible to create such a rule to a this kind of SQL query? I tried looking around, and usually people want to exclude weekends. Many thanks in advance to you all.

gaps-and-islands window-functions count sql-server sql
1 Answers

I find that is easier to use lag() and a window sum to implement the logic you want:

select employee, min(actionDate) actionStart, max(actionDate) actionEnd, count(*) actionLength
from (
    select 
        a.*, sum(
            case when actionDate = dateadd(day, 1, lagActionDate) 
                or (actionDate = dateadd(day, 3, lagActionDate) and datename(weekday, actionDate) = 'Monday')
            then 0 else 1 end
        ) over(partition by employee order by actionDate) grp
    from (
        select 
            a.*, 
            lag(actionDate) over(partition by employee order by actionDate) lagActionDate
        from actions a
    ) a
) a
group by employee, grp

Demo on DB Fiddle:

employee | actionStart | actionEnd  | actionLength
:------- | :---------- | :--------- | -----------:
AA       | 2019-01-03  | 2019-01-08 |            4
BB       | 2019-08-01  | 2019-08-06 |            6
CC       | 2019-09-09  | 2019-09-18 |            8
user_106767160
Related