How to write a StorProc to find total working days for an employee in specified month?

Question

0.00/5 (No votes)

See more:

, +

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 1:20am

iamFahhad

Add a Solution

Comments

CHill60 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 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 1-Mar-13 4:51am

Hi Check my Following post

http://www.codeproject.com/Answers/554790/Howplustoplusfindplustheplusworkingplusdaysplusfor#answer1

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bhushan Shah1988 · Answer 1 · 2013-02-27T01:47:00

First of all find out total working days in specified month with the help of following user defined function :

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 like

SQL

select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from Leavetable Where empid = 1)

This query will give you total working days as par your requirement.

Hrushikesh_phapale · Answer 2 · 2013-02-28T21:55:00

-- ================================================

SQL

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)

How to write a StorProc to find total working days for an employee in specified month?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0