By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

How to find working days for an employee in a specific Month & Year (Eg: Aug, 2012)?

P: 4
I have created a database with two fields "EmpID" (int) and "LeaveDate (DateTime) using SQL Server 2005.

How Do I modify the below Stored Procedure to find the total working days for an employee in a specified Month and Year(Eg: August, 2012)?

The Total Working Days is as follows;

Total Working Days = (Total days in a month) - ( (Weekends: Sat and Sun) + (Number of days' Leave taken by the employee) ).

The database fields may be changed as per the requirements.

How to change the below specified code to able able to find the Total Working Days for a specified Month and Year (Eg: April, 2011)?



Expand|Select|Wrap|Line Numbers
  1. /*
  2. select dbo.fnGetBusinessDaysInMonth(getdate())
  3. */
  4.  
  5. CREATE FUNCTION dbo.fnGetBusinessDaysInMonth(@currentDate datetime)
  6. returns int
  7. as
  8. begin
  9.  
  10. declare @dateRange int
  11. declare @beginningOfMonthDate datetime, @endOfMonthDate datetime
  12.  
  13. -- Get the beginning of the month
  14. set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))
  15.  
  16. -- Get the the beginning date of the next month
  17. set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))
  18.  
  19. -- Get the date range between the beginning and the end of the month
  20. set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)
  21.  
  22. return
  23. (
  24. -- Get the number of business days by getting the number
  25. -- of full weeks * 5 days a week plus the number days remaining
  26. -- minus any days from the remaining days that are a weekend day
  27. select    @dateRange / 7 * 5 + @dateRange % 7 - 
  28. (
  29. select    count(*)
  30. from
  31. (
  32. select 1 as d
  33. union
  34. select 2
  35. union
  36. select 3
  37. union
  38. select 4
  39. union
  40. select 5
  41. union
  42. select 6
  43. union
  44. select 7
  45. ) weekdays
  46. where    d <= @dateRange % 7
  47. and     datename(weekday, dateadd(day, -1, @endOfMonthDate) - d) in ('Saturday', 'Sunday')
  48. )
  49. )
  50.  
  51. end


Then write select query as follows:

Expand|Select|Wrap|Line Numbers
  1. select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from EmpTable Where EmpID = 123)
Mar 1 '13 #1
Share this Question
Share on Google+
1 Reply


Uncle Dickie
P: 67
Someone else will no doubt come along and say the same, but please use the [CODE/] tags when putting up your code.

The following works out the number of working days between 2 dates (assuming Sat and Sun are non-working)

Expand|Select|Wrap|Line Numbers
  1. DECLARE @start datetime
  2. SET @start = '2013-12-01'
  3.  
  4. DECLARE @end datetime
  5. SET @end = '2014-01-01'
  6.  
  7. DECLARE @fix int
  8. SET @fix = CASE
  9.              WHEN datepart(w,@start) = 1 THEN -1
  10.              WHEN datepart(w,@end) = 7 THEN -1
  11.              WHEN datepart(w,@end) = 1 THEN 1
  12.              ELSE 0
  13.            END
  14.  
  15. SELECT  datediff(d, @start, @end) - (datediff(ww, @start, @end) * 2) + @fix
Mar 1 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.