Count Consecutive vacation days skip through holidays and weekends

Question

I have a table which has records of user's vacation days. A Sample of that would be:

+---------+-----------+---------+------------+
| country | user_name | user_id |  vac_date  |
+---------+-----------+---------+------------+
| canada  | James     |    1111 | 2015-02-13 |
| canada  | James     |    1111 | 2015-02-17 |
| canada  | James     |    1111 | 2015-02-18 |
| canada  | James     |    1111 | 2015-02-10 |
| canada  | James     |    1111 | 2015-02-11 |
+---------+-----------+---------+------------+

With the above data, the count would be 3 from feb 13th to feb 18th, because 14th and 15th are weekends and the 16th is a holiday here in Canada. So essentially, I am trying to hold and continue the count if the user took the next working day off. I also have a table that has all the holidays which includes the country and the date of the holiday. Sample data for the holiday table would be:

+---------+-------------+-------------+
| country | holidayDesc | holidayDate |  
+---------+-------------+-------------+
| canada  | Family Day  | 2015-02-16  |  
+---------+-------------+-------------+

Currently i have a query in SQL that counts the the dates normally, so it only counts whatever is in the vacation table. For example: if a user took march 3rd 2015, march 4th 2015, and march 5th 2015 off, then it will have a count of 3, but for that above table example, it would only have a count of 1 for feb 13th and 2 from feb 17th to feb 18th.

SELECT DISTINCT user_name
    ,min(vac_date) as startDate
    ,max(vac_date) as endDate
    ,datediff(day, min(vac_date), max(vac_date)) as consecutiveCount
FROM (
    SELECT user_name
        ,vac_date
        ,user_id
        ,groupDate = DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY user_id ORDER BY vac_date
                ), vac_date)
    FROM mytable
    WHERE country = 'canada'
        AND vac_date BETWEEN '20150101'
            AND '20151231'
    ) z
GROUP BY user_name
    ,groupDate
HAVING datediff(day, min(vac_date), max(vac_date)) >= 0
ORDER BY user_name
    ,min(vac_date);

This is what it currently outputs from the above sample data:

+-----------+------------+------------+------------------+
| user_name | startDate  |  endDate   | consecutiveCount |
+-----------+------------+------------+------------------+
| James     | 2015-02-10 | 2015-02-11 |                2 |
| James     | 2015-02-13 | 2015-02-13 |                1 |
| James     | 2015-02-17 | 2015-02-18 |                2 |
+-----------+------------+------------+------------------+

Ideally i would like it to be:

+-----------+------------+------------+------------------+
| user_name | startDate  |  endDate   | consecutiveCount |
+-----------+------------+------------+------------------+
| James     | 2015-02-10 | 2015-02-11 |                2 |
| James     | 2015-02-13 | 2015-02-18 |                3 |
+-----------+------------+------------+------------------+

But i don't know if that is possible with pure SQL. I can also try to incorporate it into C#.

If it helps I am also using C# and SQL Server Management Studio. Any help would be appreciated. Thanks in advance

Answer 1

I try to go a different route, but then found the fix for John Cappelletti solution.

First you need to add weekend dates to your holiday table.

Get a list of dates between two dates using a function

Then UNION ALL vacation days with holidays, but add a description field so you can difference between both.

There are some CROSS JOIN so you can have holiday and weekends for each country and user (need testing)

SELECT [country], 
       [user_name], [user_id], [vac_date], 'vacation' as description
FROM vacations
UNION ALL 
SELECT c.[country], 
       u.[user_name],
       u.[user_id],
       [holidayDate], 
       'holiday' as description
FROM holidays     
CROSS JOIN (SELECT DISTINCT [country] FROM vacations) c
CROSS JOIN (SELECT DISTINCT [user_name], [user_id] FROM vacations) u  

Then the final query is the same as John suggested, but this time you only count vacation days.

WITH joinDates as (
    SELECT [country], 
           [user_name], [user_id], [vac_date], 'vacation' as description
    FROM vacations
    UNION ALL 
    SELECT c.[country], 
           u.[user_name],
           u.[user_id],
           [holidayDate], 
           'holiday' as description
    FROM holidays     
    CROSS JOIN (SELECT DISTINCT [country] FROM vacations) c
    CROSS JOIN (SELECT DISTINCT [user_name], [user_id] FROM vacations) u    
)    
Select user_name
      ,startDate = min(vac_date)
      ,endDate   = max(vac_date)
      ,consecutiveCount = count(*)
From  (
        Select *
              ,Grp =  Day(vac_date) - Row_Number() over (Partition By country,user_id 
                                                         Order by vac_date)
         From  joinDates S
      ) A
WHERE description = 'vacation'    -- only count vacation days ignore holiday/weekend   
Group By user_name, Grp
Having count(*)>1
ORDER BY startDate

SQL DEMO

OUTPUT

RAW OUTPUT

here you can see the data before the group by

Answer 2

This seems like a classic Gaps & Islands with a little twist.

Declare @YourTable table (country varchar(25),user_name varchar(25),user_id varchar(25),vac_date date)
Insert Into @YourTable values
('canada','James','1111','2015-02-13'),
('canada','James','1111','2015-02-17'),
('canada','James','1111','2015-02-18'),
('canada','James','1111','2015-02-10'),
('canada','James','1111','2015-02-11')

Declare @Holiday table (country varchar(25),holidayDate date)
Insert Into @Holiday values
('canada','2015-02-16')

Select user_name
      ,startDate = min(vac_date)
      ,endDate   = max(vac_date)
      ,consecutiveCount = sum(DayCnt)
From  (
        Select *
              ,Grp =  Day(vac_date) - Row_Number() over (Partition By country,user_id Order by vac_date)
         From  (Select Country,user_name,user_id,vac_date,DayCnt=1 from @YourTable
                Union All
                Select A.Country,user_name,user_id,vac_date=b.holidayDate,DayCnt=1
                 From  @YourTable A
                 Join  @Holiday B on A.country=B.country and abs(DateDiff(DD,vac_date,holidayDate))=1
                Union All
                Select A.Country,user_name,user_id,vac_date=b.retval,DayCnt=0
                 From  @YourTable A
                 Join  (
                        Select * From [dbo].[udf-Range-Date]('2015-01-01','2017-12-31','DD',1) where DateName(WEEKDAY,RetVal) in ('Saturday','Sunday')
                       ) B on abs(DateDiff(DD,vac_date,RetVal))=1

               ) S
      ) A
 Group By user_name,Grp
 Having Sum(DayCnt)>1

Returns

user_name   startDate   endDate     consecutiveCount
James       2015-02-10  2015-02-11  2
James       2015-02-16  2015-02-18  3

The UDF to generate dynamic Date Ranges -- could be your own query

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Answer 3

OK, my understanding of the question is that what you want to do is count spans of days off as only one day. Many businesses call this an "occurrence of absence" to differentiate absences by cause. In this case, you're trying to treat holidays as a continuance of the holiday (for time purposes) and if a holiday occurs on a Friday but the person takes Monday off, that should be one contiguous time out.

Personally, I'd do this in C# because of properties of the DateTime object that could make this a lot easier than trying to make a frankenquery. The code below assumes that you have an object called an Employee that contains its own record of DateTimes, like so:

public class Employee
{
     public int ID {get;set;}
     public string Name {get;set;}
     public List<DateTime> DaysIWasOut {get;set;}
}

public static int TimeOut(IEnumerable employees)
{     
       int totalOutInstances = 0;
       DataTable dt = HolidaysPlease(); //this refers to another method
       //to fill the table.  Just a basic SQLAdapter.Fill kind of thing. 
       //Basic so I won't waste time on it here.
       foreach(var e in employees)
       {
         var holidays = dt.AsEnumerable().Where(t => Convert.ToDateTime(t[3]) == d)        //holidays now has all of the holidays the employee had off. 
         totalOutInstances = e.DaysIWasOut.Count();
         foreach(var d in e.DaysIWasOut)
         {
            int daystolook = 0;
            if (d.DayOfWeek == DayOfWeek.Friday)
               daystolook +=3;
            else
               daystolook +=1;
            if(e.DaysIWasOut.Contains(d.AddDays(daystolook))                        
                   {totalOutInstances --; } //don't count that day               
         }

    }
 return totalOutInstances;
}