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

[Fahhad]

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)?

/*
select dbo.fnGetBusinessDaysInMonth(getdate())
*/

CREATE FUNCTION dbo.fnGetBusinessDaysInMonth(@currentDate datetime)
returns int
as
begin

declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime

-- Get the beginning of the month
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))

-- Get the the beginning date of the next month
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))

-- Get the date range between the beginning and the end of the month
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)

return
(
-- Get the number of business days by getting the number
-- of full weeks * 5 days a week plus the number days remaining
-- minus any days from the remaining days that are a weekend day
select	@dateRange / 7 * 5 + @dateRange % 7 - 
(
select	count(*)
from
(
select 1 as d
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
) weekdays
where	d <= @dateRange % 7
and	 datename(weekday, dateadd(day, -1, @endOfMonthDate) - d) in ('Saturday', 'Sunday')
)
)

end

Then write select query as follows:

select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from EmpTable Where EmpID = 123)

[Uncle Dickie]

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)

DECLARE @start datetime
SET @start = '2013-12-01'

DECLARE @end datetime
SET @end = '2014-01-01'

DECLARE @fix int
SET @fix = CASE
             WHEN datepart(w,@start) = 1 THEN -1
             WHEN datepart(w,@end) = 7 THEN -1
             WHEN datepart(w,@end) = 1 THEN 1
             ELSE 0
           END

SELECT  datediff(d, @start, @end) - (datediff(ww, @start, @end) * 2) + @fix