First of all find out total working days in specified month with the help of following user defined function :
CREATE FUNCTION dbo.fnGetBusinessDaysInMonth(
@currentDate datetime
)
returns int
as
begin
declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)
return
(
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.