Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server Transact-SQL , +
I have created a database with two fields "EmpID" (int) and "LeaveDate" (DateTime) using SQL Server 2005.
 
How to create a stored procedure to find the total working days for an employee in a specified month?
 
The Total Working Days should be 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 implement this Stored Procedure? Please give sample queries to do this.
Posted 27-Feb-13 2:20am
Comments
CHill60 at 27-Feb-13 7:51am
   
Are you saying that the database will hold a record for each employee for every day that they have taken as leave? I.e. Emp1 '04-Feb-2013', Emp1 '05-Feb-2013', Emp1 '06-Feb-2013'
iamFahhad at 28-Feb-13 0:36am
   
No.. It is just a LeaveTable. i.e The table will only keep track of leave taken by the employees. Only if any employee takes a leave, his/her employeeID and date of taking leave will be stored in the table.
gvprabu at 1-Mar-13 4:51am
   
Hi Check my Following post
 
http://www.codeproject.com/Answers/554790/Howplustoplusfindplustheplusworkingplusdaysplusfor#answer1
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

First of all find out total working days in specified month with the help of following user defined function :
 
/*
	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 like
 
select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from Leavetable Where empid = 1)
 
This query will give you total working days as par your requirement.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

-- ================================================
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION GetWorkingDays
(
	@Date as Datetime,
	@Leaves as int
)
RETURNS  int
AS
BEGIN
	Declare @Total as int
	 
declare @DW int
 
declare @Cnt int
 
declare @FDt Datetime
 
declare @LDt Datetime
 
SELECT @FDt=DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
 
SELECT @LDt=DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
 
SET @CNT=0
 
While(@FDt<=@LDt)--Date is not a last date

begin
 
select @DW=DATEPART(DW,@FDt)
 
set @FDt=@FDt+1
 

While(@DW!=7 AND @DW!=1)
 
begin
 
SET @CNT=@CNT+1
 
break 
 
End
 
END
 
 set @Total=@CNT-@Leaves
	-- Return the result of the function
	RETURN @Total
END
GO
 

select dbo.GetWorkingDays('1-Mar-2012',2)
  Permalink  
v3

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 9,535
1 OriginalGriff 8,295
2 Peter Leow 4,839
3 Kornfeld Eliyahu Peter 3,210
4 Maciej Los 2,301


Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 1 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100