Find number of weekdays(Monday,Tuesday..) between two dates monthwise

create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date))
go
insert into dbo.Calendar (Date, Workday)
select dateadd(day, number, '1/1/2000'),
case
when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1
else 0
end
from dbo.numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0
where month = 7 and day = 4
or ... -- fill in other holidays here
go
select count(*)
from dbo.Calendar
where workday = 1
and date between '5/20/2008' and '6/9/2008'

;with Thanksgiving (Row, Date, Workday) as
(select row_number() over (order by date), date, workday
from dbo.Calendar
where Month = 11
and WeekDay = 5)
update Thanksgiving
set workday = 0
where row = 4

GSquared (6/9/2008)

For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.

create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime 
  check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date))
go
insert into dbo.Calendar (Date, Workday)
select dateadd(day, number, '1/1/2000'),
case
  when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1
  else 0
end
from dbo.numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0
where month = 7 and day = 4
or ... -- fill in other holidays here
go
select count(*)
from dbo.Calendar 
where workday = 1
and date between '5/20/2008' and '6/9/2008'

With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.

;with Thanksgiving (Row, Date, Workday) as
  (select row_number() over (order by date), date, workday
  from dbo.Calendar
  where Month = 11
  and WeekDay = 5)
update Thanksgiving
set workday = 0
where row = 4

You can also add more indexes to it, if you so desire.

Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).

CREATE FUNCTION [dbo].[IF_Calendar]
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
-- inline tally table
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max

iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)

-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno = DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName] = DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
WHERE b.FirstWeekDay = @FirstWeekDay

)