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

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jan 18 '17 at 17:19
Sorry about the formatting, i fixed the table. Let me know if there's anything else. — dest, Jan 18 '17 at 17:38
I have added a sample of the holiday table as well. Let me know if there's anything else. — dest, Jan 18 '17 at 17:51
It's not clear to me. do you want to count the consecutive working days for Feb Second week (02/8 trough 02/14) or the week after (02/15 through 02/21) ? or maybe the whole Feb month? — FLICKER, Jan 18 '17 at 17:51
It's always good idea to show the expected result for more clear understanding — FLICKER, Jan 18 '17 at 17:53
I would like to count the consecutive working days in a year. The Above SQL query is looking through records in 2015. — dest, Jan 18 '17 at 17:55
My bad, i want the vacations days that are in the vacation table. I have corrected the title. — dest, Jan 18 '17 at 18:34

John Cappelletti · Answer 1 · 2017-01-18T19:32:17.673

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) 
*/

Hi there Jhon, I think is very close, but beside holiday you also need to include the weekends somehow. Also starDate and endDate, shouldnt be the same — Juan Carlos Oropeza, Jan 18 '17 at 18:35
@JuanCarlosOropeza thanks. I overlooked that part - will make an update. — John Cappelletti, Jan 18 '17 at 18:37
I think is easier if you insert the weekends into the holiday table. — Juan Carlos Oropeza, Jan 18 '17 at 18:38
I am able to dynamically select the weekend dates from a certain year, would i just add another union all statement and have the same conditions? And thank you so much for your help. — dest, Jan 18 '17 at 19:10
@RonnieHuang Sure, assuming mon-friday worker, but I'm having a hard time understanding how weekends count towards consecutive days. would they be 1 or a 0? — John Cappelletti, Jan 18 '17 at 19:14
@JohnCappelletti I'm not sure i follow, this is for mon-fri worker. So if the worker took a vacation day on friday, the counter would continue on if they also took monday offf. Let me know if you need more clarification. — dest, Jan 18 '17 at 19:20
@RonnieHuang added a union all and DayCnt. Weekends count as 0 so not to inflate the consecutiveDays — John Cappelletti, Jan 18 '17 at 19:33
@RonnieHuang Just to elaborate. If worker took a vacation days on Friday and Monday this would now count as 2 days not 4... correct? — John Cappelletti, Jan 18 '17 at 19:36
@JohnCappelletti yes that would count as 2 days. The weekends and holidays should not be included in the counter. — dest, Jan 18 '17 at 19:55

score 1 · Accepted Answer · edited May 23 '17 at 12:01

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

@JohnCappelletti I just found an error with the grp, the `2015-02-28` and `2015-03-01` appear on different grp. Do you know why? — Juan Carlos Oropeza, Jan 18 '17 at 20:31
@JuanCarlosOropeza Thanks a lot, I will test it more thoroughly tomorrow. It's a shame i cannot accept both your and John's answer. — dest, Jan 18 '17 at 22:40

score 0 · Answer 3 · answered Jan 18 '17 at 18:35

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;
}

If possible i would like to do it in SQL, if that becomes too problematic, I'll try your solution. Thanks for your help. — dest, Jan 18 '17 at 19:11

Count Consecutive vacation days skip through holidays and weekends

3 Answers3