Hi,

I have a table containing 7 fields, Mon, Tues, Wed,Thur,Frid,Sat and
Sub, all bit types. I need to workout the next run date based on the
above fields having a value of 1, example:

Today is Wed 24/08/2005

Mon =1, Tue =0, Wed=0, Thurs=0, Frid=0, Sat=0 and Sunday=0

What is the simplest way to work out the date based on the above.

Thanks in advance
Paul.

PP



CREATE TABLE #Test (wid INT NOT NULL PRIMARY KEY,
week_day CHAR(3), value BIT NOT NULL)

INSERT INTO #Test VALUES (1,'Mon',0)
INSERT INTO #Test VALUES(2,'Tue',0)
INSERT INTO #Test VALUES(3,'Wed',0)
INSERT INTO #Test VALUES(4,'Thu',0)
INSERT INTO #Test VALUES(5,'Fri',0)
INSERT INTO #Test VALUES(6,'Sat',0)
INSERT INTO #Test VALUES(7,'Sun',0)


SELECT * FROM #Test

UPDATE #Test SET value =1 WHERE wid=(SELECT wid=wid+1 FROM #Test
WHERE week_day=LEFT(DATENAME(WEEKDAY, getdate()),3))







[quoted text, click to view]

This would be much easier if you stored the days in the
table, not in the columns of the table, but here's a solution:

declare @today datetime;
set @today = '20050824';
declare @basedate datetime;
set @basedate = '18991231';
select min(runDate)
from (
select
dateadd(day,datediff(day,@basedate+oneDay,@today)/7*7,@basedate+oneDay+7)
as runDate
from ( -- You wouldn't need the mess below with a better table for the
run days.
select case when Mon = 1 then 1 end as oneDay from Rundays union all
select case when Tues = 1 then 2 end as oneDay from Rundays union all
select case when Wed = 1 then 3 end as oneDay from Rundays union all
select case when Thur = 1 then 4 end as oneDay from Rundays union all
select case when Frid = 1 then 5 end as oneDay from Rundays union all
select case when Sat = 1 then 6 end as oneDay from Rundays union all
select case when Sub = 1 then 7 end as oneDay from Rundays
) R
where oneDay is not null
) as T

Steve Kass
Drew University

[quoted text, click to view]