create function f_count_business_day(@start datetime, @end datetime)
returns int
begin
	declare @cnt int

	select @cnt  = count(*) 
        from   F_TABLE_DATE(@start, @end) 
        where  WEEKDAY_NAME not in ('Sat', 'Sun')

	return @cnt
end

SELECT Date_Booked, dbo.f_count_business_day(Date_Booked, GETDATE()) AS NumberOfDays
from   table

drop function dbo.F_GET_WORK_DAYS
go
create function dbo.F_GET_WORK_DAYS
	(
	@START_DATE		datetime,
	@END_DATE		datetime
	)
returns int
as
/*
Find the total workdays (Monday to Friday) between two dates,
including the start and end date.
*/

begin

declare @LAST_WEEK_START	datetime
declare	@WHOLE_WEEKS		int
declare	@WHOLE_WEEK_WORKDAYS	int
declare @LAST_WEEK_START_DW	int
declare @LAST_WEEK_DAYS		int
declare @LAST_WEEK_WORKDAYS	int
declare @WORKDAYS		int

set @WHOLE_WEEKS = (datediff(dd,@START_DATE,@END_DATE)+1)/7

set @WHOLE_WEEK_WORKDAYS =  @WHOLE_WEEKS*5

set @LAST_WEEK_START = dateadd(dd,@WHOLE_WEEKS*7,@START_DATE)

set @LAST_WEEK_START_DW = datediff(dd,'17530101',@LAST_WEEK_START)%7

set @LAST_WEEK_DAYS = datediff(dd,@LAST_WEEK_START,@END_DATE)+1

set @LAST_WEEK_WORKDAYS = @LAST_WEEK_DAYS -
	case
	when @LAST_WEEK_DAYS = 0	then 0
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 6 	then 2
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 5 	then 1
	when @LAST_WEEK_START_DW = 0 				then 0
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 5 	then 2
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 4 	then 1
	when @LAST_WEEK_START_DW = 1 				then 0
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 4 	then 2
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 3 	then 1
	when @LAST_WEEK_START_DW = 2 				then 0
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 3 	then 2
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 2 	then 1
	when @LAST_WEEK_START_DW = 3 				then 0
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 2 	then 2
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 1 	then 1
	when @LAST_WEEK_START_DW = 4 				then 0
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 1 	then 2
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 0 	then 1
	when @LAST_WEEK_START_DW = 5 				then 0
	when @LAST_WEEK_START_DW = 6 and @LAST_WEEK_DAYS > 6 	then 2
	else 1
	end

set @WORKDAYS = @WHOLE_WEEK_WORKDAYS + @LAST_WEEK_WORKDAYS

return @WORKDAYS

end
go

quote:

---

Originally posted by khtan

So i assume the F_GET_WORK_DAYS will also be to script lib ?

---

KH

---

quote:

---

Originally posted by rwaldron

Hi Guys ths for your help so far.
I have now used the finction F_GET_WORK_DAYS in the following query.
I have also included a datediff function just to compare the results.
Is F_GET_WORK_DAYS Inclusive of today ? Results seem to be 1 day out.

Query is

SELECT Date_Booked, dbo.f_get_work_days(Date_Booked, GETDATE()) AS BusinessDays_usingFunct,
DATEDIFF([day], Date_Booked, GETDATE()) AS FullDays_using_Date_diff
from tbl11_SWC_Open_Service_Orders
order by date_booked desc

results

Date_Booked BusinessDays_usingFunct FullDays_using_Date_diff

2007-03-09 2 3
2007-03-09 2 3
2007-03-09 2 3
2007-03-09 2 3
2007-03-05 6 7
2007-03-02 7 10
2007-03-02 7 10
2007-03-02 7 10
2007-03-02 7 10
2007-03-01 8 11

---