13,512,345 members
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
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'

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

## Solution 1

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

```/*
*/
@currentDate datetime
)
returns int
as
begin

declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime

-- Get the beginning of the month

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

## 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

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)```
v3

Top Experts
Last 24hrsThis month
 OriginalGriff 415 Jochen Arndt 360 Maciej Los 275 Richard Deeming 225 CPallini 145
 OriginalGriff 5,256 Jochen Arndt 3,093 Maciej Los 2,429 ppolymorphe 2,382 Wendelius 2,223