Get month wise Number of working

Sql server:Get month wise Number of working days in Year.

Hi,

By Using the following query we can get a Month wise number of working days in year.

 
        declare @StartDate datetime ,
                   @EndDate datetime ,
                   @Month int ,
                   @NoOfDays int
                   set @Month = 1
                  set @NoOfDays = 0
-- Temporary Table to store Month Wise No Of Working Days
           declare @tab as Table
      (
                 Mon int ,
                  NoOfWorkingDays int
)
while @Month <= 12
begin
set @StartDate = cast ( cast ( year ( getdate ()) as varchar(4))+'-'+cast(@Month as carchar (2))+'-01' as datetime )
set @NoOfDays = datepart (day, dateadd (s,-1, dateadd (mm, datediff(m,0,@StartDate)+1,0)))
set @EndDate = cast ( cast ( year ( getdate()) as varchar (4))+'-'+ cast (@Month as varchar (2))+'-'+ cast (@NoOfDays as varchar (2)) as datetime )
insert into @tab
selete @Month,
( datedeff(dd, @StartDate, @EndDate) + 1)
-( datedeff(wk, @StartDate, @EndDate))
-( case when datename(dw, @StartDate) = 'Sunday' then 1 else 0 end )
set @Month = @Month + 1
end
-- To Display Month Wise Number Of Working Days for the Current Year
selete case Mon
when 01 then 'JANUARY'
when 02 then 'FEBRUARY'
when 03 then 'MARCH'
when 04 then 'APRIL'
when 05 then 'MAY'
when 06 then 'JUNE'
when 07 then 'JULY'
when 08 then 'AUGUST'
when 09 then 'SEPTEMBER'
when 10 then 'OCTOBER'
when 11 then 'NOVEMBER'
when 12 then 'DECEMBER'
end as 'MONTH' , NoOfWorkingDays
from @tab

If You need to calculate total number of working days in year then just get
 sum(NoOfWorkingDays)