try this

Declare @start_dte as datetime = '01-Apr-2014'

, @end_dte as datetime = '30-Apr-2014'

Declare @WeekDayTable Table ( colWeekDayID tinyint

, ColWeekDayName varchar(20)

, ColIsWeekEnd bit

)

Declare @HolidayTable Table ( ID int identity(1,1)

, HolidayDt Datetime

)

insert into @WeekDayTable

Select 1,'Sunday', 0 union all

Select 2,'Monday', 1union all

Select 3,'Tuesday', 1union all

Select 4,'Wednesday', 1 union all

Select 5,'Thursday', 1union all

Select 6,'Friday', 1union all

Select 7,'Saturday', 0;

insert into @HolidayTable

Select '02-Apr-2014';

;With cCalender

AS

(

Select N AS ID, dateadd(dd,N-1,@start_dte) AS CalenderDate

, DATEPART(DW,dateadd(dd,N-1,@start_dte)) as WeekDays

, DATENAME(DW,dateadd(dd,N-1,@start_dte)) as WeekDayName

from

(

Select top 10000 row_number() Over (Order by o.id) as N

from sysObjects o

cross join sysobjects oo

) Tally

Where dateadd(dd,N-1,@start_dte) <= @end_dte

)

select count(*) AS NoOfBussinessDays

from cCalender c

Where Not Exists ( Select 1

From @WeekDayTable w

Where c.WeekDayName = w.ColWeekDayName

And ColIsWeekEnd = 0

)

And Not Exists ( select 1

from @HolidayTable h

where h.HolidayDt = c.CalenderDate

)

Hope it helps