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:
- leave_start
- leave_end
- leave_working_days
Here is the output:
Do let me know your way to tackle this problem – by way of leaving a comment.
Enjoy!
Regards
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
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))
Results.
2012-10-20 12:37:57.607 2012-10-28 12:37:57.607 4
it should be 5
precisely, thanks Kishor for pointing this issue.
Thanks VVery Much Kishor, i found the issue and it has been resolved, thanks for your time
I’ve re-written the whole script and now it works for all scenarios.
–with no bugs 😉
Script Link Updated