Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)?

SQL
/*
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:

SQL
select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from EmpTable Where EmpID = 123)
Posted
Updated 28-Feb-13 17:50pm
v2

1 solution

Hi IamFahhad,

Try This .....
SQL
-- Number of working days between two dates
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT

SELECT @ReqMonth=11,@ReqYear =2010

SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' 
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))

SELECT @fromDate , @toDate 

SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1) 
        - (DATEDIFF(WEEK, @fromDate, @toDate) * 2)  
        - (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END) 
        - (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
	   -- No of Holidays also we need to deduct.

Regards,
GVPrabu
 
Share this answer
 
v3
Comments
iamFahhad 1-Mar-13 0:23am    
Hi Prabu,

Where do I insert that piece of code?

What do you think about using MONTH(08) and YEAR(2012)?

I do not want to hard-code the date into the Stored Procedure. I should be able to enter the date as an input.
gvprabu 1-Mar-13 0:30am    
Hi,
I will try this and give u the solution
gvprabu 1-Mar-13 0:42am    
Hi,

I updated my Solution... check like this. I think it will help u

:-)
iamFahhad 1-Mar-13 1:27am    
Hi,

Your sql code gets successful. I put it in a procedure "cat". But it does not give any output. When I select it as follows:
select dbo.cat

I'm getting an error as follows:

Msg 4104, Level 16, State 1, Line 124
The multi-part identifier "dbo.cat" could not be bound.
gvprabu 1-Mar-13 1:48am    
I am not getting your error, Share your Procedure.
If "select dbo.cat" means you need to specify the column names also right...
like "select Column_Name FROM dbo.cat".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900