How to write a Stored Procedure to find the total working days for an employee in a specified month?

Question

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.

Answer 1

DECLARE @DATE DATETIME

SET @DATE = GETDATE()

SELECT  20 + COUNT(*)
FROM   (
    	SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE), 28) AS THEDATE UNION ALL
    	SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE), 29) UNION ALL
    	SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE), 30)
    	) AS DAYS
WHERE DATEPART(DAY, THEDATE) > 28 AND DATEDIFF(DAY, 0, THEDATE) % 7 < 5
Months will always have 20 working days in the first 28 days of the month. 
You'll only need to check for the last remaining days of the month with the above code.

Answer 2

you may need to pass the year also along with month, else for February month you will get some differences.

1. get total number of LeaveDate in that month from that table.

select COUNT(*) from Table1 
where DATEPART(month, LeaveDate) = DATEPART(month, GETDATE()) -- use the date where you want to calculate the months' data
AND DATEPART(year, LeaveDate) = DATEPART(year, GETDATE()) -- use the date where you want to calculate the months' data

2. Get total weekdays or weekends in that month of the year : http://blog.tech-cats.com/2008/02/get-business-days-and-working-hours-in.html,

http://sqltutorials.blogspot.se/2007/06/sql-statement-get-total-working-day-in.html

Get your result by substracting result of 1 from result 2.

regards

joon

Answer 3

The simplest solution is to use a Calendar table.

See also this blog post

Calculating number of workdays between 2 dates

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Hi Fahhad,

You need a loop to check all of the days of the month. You should also have a table in which the details of leave days of the employees are stored. Use this

select case substring(datename(dw,cast('12-31-12' as datetime)),1,3) when 'Sat' then 'Yes' when 'Sun' then 'Yes' else 'No' end
go

to exclude those weekends.

---

Many Thanks & Best Regards, Hua Min