Hi Duncan,

you first need to decide which days are weekend by using the datepart function and create a list of every day between the 2 stating which is a weekend. then sum the difference.

Excluding Holidays is more involved as you have to individually update the table to show which days have been holidays which SQL doesn't store.an example using the code below could be:

update #temp

set workdayind = 0

where fulldate = 'Date of holiday'

but you'd need one per holiday.

set dateformat ymd

set datefirst 1

declare @StartDate datetime

declare @endDate datetime

set @StartDate = cast('2014/01/31' as datetime) ---- replace with startdate

set @EndDate = DATEADD(dd, -1, left(getdate(), 11)) ----- replace with endDate

create table #temp

(Fulldate datetime, WorkingDayind tinyint)

Repeat:

set @Startdate = @StartDate + 1

insert into #temp

(Fulldate, WorkingDayind)

select @StartDate,

case when datepart(dw, @StartDate) <= 5 then 1 else 0 end

if @StartDate < @endDate goto Repeat

select SUM(WorkingDayind) from #temp

select * from #temp

drop table #temp

I'm more than open to a simpler way of doing it but this was the only way I could think of at the time.

J