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 Stored Procedure to able able to find the Total Working Days for a specified Month and Year (Eg: April, 2011)?
CREATE FUNCTION dbo.fnGetBusinessDaysInMonth(@currentDate datetime)
returns int
as
begin
declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)
return
(
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)