Declare @DateShipped datetime,
        @DateDelivered Datetime,
		@DayDiff int,
		@x int

Set @DateShipped = '2006/06/17'
Set @DateDelivered = '2006/06/24'

If Datename(dw,@DateShipped) = 'Saturday'					--If Shipped date falls on a Sat then Move the date to the following Date
	Begin													--If it falls on a Sunday then move it to Monday.
		Set @DateShipped = Dateadd(dd, 2,@DateShipped)		--This logic applies to the Delivery date.
	End
Else
	If Datename(dw,@DateShipped) = 'Sunday' 
		Begin
			Set @DateShipped = Dateadd(dd, 1,@DateShipped)
		End

If Datename(dw,@DateDelivered) = 'Saturday' 
	Begin
		Set @DateDelivered = Dateadd(dd, 2,@DateDelivered)
	End
Else
	If Datename(dw,@DateDelivered) = 'Sunday' 
		Begin
			Set @DateDelivered = Dateadd(dd, 1,@DateDelivered)
		End

Select @DayDiff = Datediff(d, @DateShipped, @DateDelivered)
Print @DayDiff												--[Totals Days (incld Wkends)]

Select @x = Sum(@DayDiff/7)
if @x >= 1
	Begin
		
		Set @DayDiff = @DayDiff-(@x*2)
	End

Print @DayDiff												--[Totals Days (excld Wkends)]
	

drop table #ttr_calendar

CREATE TABLE [dbo].[#ttr_calendar] (
	[day_number] [varchar] (50) NOT NULL ,
	[day_name] [varchar] (50) NULL ,
	[begin_time] [datetime] NULL ,
	[end_time] [datetime] NULL ,
	[duration] [real] NULL 
) ON [PRIMARY]

insert into #ttr_calendar
select 1,             'Monday',      '6:30:00 AM',    '5:00:00 PM',   37800 union all
select 2,             'Tuesday',     '6:30:00 AM',    '9:00:00 PM',   52200 union all
select 3,             'Wednesday',   '6:30:00 AM',    '9:00:00 PM',   52200 union all
select 4,             'Thursday',    '6:30:00 AM',    '9:00:00 PM',   52200 union all
select 5,             'Friday',      '6:30:00 AM',    '9:00:00 PM',   52200 union all
select 6,             'Saturday',    '8:00:00 AM',    '5:00:00 PM',   32400 union all
select 7,             'Sunday',      '8:00:00 AM',    '5:00:00 PM',   32400

declare	@start_date	datetime,
	@end_date	datetime

select	@start_date 	= '15 Feb 2007 11:00:00 AM',
	@end_date 	= '15 Feb 2007 03:00:00 PM'

select	total_hours = sum(case 	
			when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then
				datediff(second, @start_date, @end_date)
			when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
				case 	
				when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
				else duration
				end
			when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
				case	
				when @end_date	<  [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
				else duration
				end
			else duration
			end
			  ) 
			/ 60.0 / 60.0
from	F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c
	on	d.WEEKDAY_NAME_LONG = c.day_name

CREATE TABLE [dbo].[#ttr_calendar] (
	[row_id] [varchar] (50) NOT NULL ,
	[start_date] [datetime] NULL ,
	[end_date] [datetime] NULL ,
	[total_hours] [real] NULL 
) ON [PRIMARY]

create function fn_workinghour
(
	@start_date	datetime,
	@end_date	datetime
)
returns decimal(10,2)
as
begin
	declare @total_hours	decimal(10,2)

	select	@total_hours = sum(case 	
				when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then
					datediff(second, @start_date, @end_date)
				when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
					case 	
					when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
					else duration
					end
				when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
					case	
					when @end_date	<  [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
					else duration
					end
				else duration
				end
				  ) 
				/ 60.0 / 60.0
	from	F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
		on	d.WEEKDAY_NAME_LONG = c.day_name

	return @total_hours
end

update t
set    total_hours = dbo.fn_workinghour(start_date, end_date)
from   yournewtable t

from	F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
		on	d.WEEKDAY_NAME_LONG = c.day_name
        left join holiday h
                on      d.DATE              = h.DATE
where   h.DATE is NULL