-- Select date range for update
declare @start_date	datetime
declare @end_date	datetime
-- 20 Days before today
select @start_date = dateadd(dd,datediff(dd,0, getdate()-20),0)
-- 40 Days after start date
select @end_date	= @start_date+40

select	[@start_date] = convert(varchar(10),@start_date,121), 
	[ @end_date] =  convert(varchar(10),@end_date,121)

-- Holiday table
declare @holiday table	
([DATE]  datetime not null primary key clustered )

print '***  Load  @holiday'
insert into @holiday
select '20060704' -- July 4th Holiday



-- Work Date Table
declare @WorkDate table	
([WORK_DATE]  datetime not null primary key clustered
 )

print '***  Load @WorkDate'
-- Load table with Work Days
insert into @WorkDate
select
	a.[DATE]
from
	-- Date Table Function from Script Library
	dbo.F_TABLE_DATE ( @start_date,@end_date) a
	left join
	@holiday b
	on a.[DATE] = b.[DATE]
where
	-- Select Monday through Friday
	a.[DAY_OF_WEEK] between 2 and 6 and
	-- Exclude Holidays
	b.[DATE] is null

-- Test Data
declare  @MyTable table (
MyTableID	int identity(1,1)  primary key clustered,
MyDate  datetime not null
)


print '***  Load @MyTable'
-- Load test dates to be updated
insert into @MyTable (MyDate)
select '20060623' union all
select '20060624' union all
select '20060625' union all
select '20060626' union all
select '20060627' union all
select '20060628' union all
select '20060704'

print '***  @MyTable before update'
select * from @MyTable

print '***  Update @MyTable'
update a
set
	a.MyDate = 
		( -- Update to earliest work date > MyDate
		select
			[DATE] = min(c.[WORK_DATE])
		from
			@WorkDate c
		where
			c.[WORK_DATE] > a.MyDate
		)
from
	@MyTable a
where
	-- Select only dates in ramge
	a.MyDate >= @start_date and
	a.MyDate < (select max(z.[WORK_DATE]) from @WorkDate z ) and
	-- Select rows that are not work days
	a.MyDate not in
	( select x.[WORK_DATE] from @WorkDate x )


print '***  @MyTable after update'
select * from @MyTable

start_date  @end_date 
----------- ---------- 
2006-06-07  2006-07-17

(1 row(s) affected)

***  Load  @holiday

(1 row(s) affected)

***  Load @WorkDate

(28 row(s) affected)

***  Load @MyTable

(7 row(s) affected)

***  @MyTable before update
MyTableID   MyDate                                                 
----------- ------------------------------------------------------ 
1           2006-06-23 00:00:00.000
2           2006-06-24 00:00:00.000
3           2006-06-25 00:00:00.000
4           2006-06-26 00:00:00.000
5           2006-06-27 00:00:00.000
6           2006-06-28 00:00:00.000
7           2006-07-04 00:00:00.000

(7 row(s) affected)

***  Update @MyTable

(3 row(s) affected)

***  @MyTable after update
MyTableID   MyDate                                                 
----------- ------------------------------------------------------ 
1           2006-06-23 00:00:00.000
2           2006-06-26 00:00:00.000
3           2006-06-26 00:00:00.000
4           2006-06-26 00:00:00.000
5           2006-06-27 00:00:00.000
6           2006-06-28 00:00:00.000
7           2006-07-05 00:00:00.000

(7 row(s) affected)

select 
	DT = convert(varchar(10), DT,121),
	DW = datepart(dw,dt),
	DName= datename(dw,DT)
from
	(select DT = getdate()+4 union all select DT = getdate()+3) a



Results:

DT         DW          DName                          
---------- ----------- ------------------------------ 
2006-07-01 7           Saturday
2006-06-30 6           Friday

(2 row(s) affected)

quote:

---

Originally posted by byrmol

>>....unless you consider Friday a weekend day.

Rob's an Aussie Michael, so when the the surf's up and the sun is hot, everyday is the weekend...

DavidM

---