# 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.
Wednesday, February 27, 2013 12:18 PM

• The simplest solution is to use a Calendar table.

# Calculating number of workdays between 2 dates

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

My blog

• Marked as answer by Friday, March 8, 2013 4:25 PM
Thursday, February 28, 2013 2:58 AM

### All replies

• ``````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.```

Wednesday, February 27, 2013 12:27 PM
• 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

• Edited by Wednesday, February 27, 2013 12:55 PM
Wednesday, February 27, 2013 12:53 PM
• The simplest solution is to use a Calendar table.

# Calculating number of workdays between 2 dates

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

My blog

• Marked as answer by Friday, March 8, 2013 4:25 PM
Thursday, February 28, 2013 2:58 AM