SQL Server T-SQL | Calculate Number of Leaves – excluding weekends

Hi Friends,

Yesterday I was answering some questions on TechNet Forum when I saw an interesting and genuine problem.

The user wanted to calculate the number of leaves taken by an employee excluding weekends. Now calculating the number of leaves wasn’t a big deal all we need to do is just use datediff function but excluding the weekends was something which forced me to think harder.

Then I started digging into this and finally I was able to make logic.

Here is the script:  (Updated)

Direct Link: (Updated)

It works pretty well

For the sake of simplicity I’m creating the table with just three columns:

  1. leave_start
  2. leave_end
  3. leave_working_days

Here is the output:



Do let me know your way to tackle this problem – by way of leaving a comment.




Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


5 Comments on “SQL Server T-SQL | Calculate Number of Leaves – excluding weekends”

  1. Hi Sarab,

    I don’t think this query works in all Condition.

    Please check below query..

    Declare @ls as DateTime
    Declare @le as DateTime
    Set @ls = GETDATE()-5;
    set @le = GETDATE()+3;
    select @ls,@le,(datediff(day,@ls,@le)-(2)*datediff(week,@ls,@le))


    2012-10-20 12:37:57.607 2012-10-28 12:37:57.607 4

    it should be 5

  2. Thanks VVery Much Kishor, i found the issue and it has been resolved, thanks for your time

  3. I’ve re-written the whole script and now it works for all scenarios.

    –with no bugs 😉

Leave a Reply

Your email address will not be published.