数据库是用sql2000
有张表a
结构如下：
a_date a_day
2013-11-11 3
2013-11-19 3
2013-11-13 5
2013-11-15 1
a_date 是登记日期 a_day 是多少工作日
我要查询到多少个工作日后的日期，要去除星期六和星期日
效果如下：
a_date a_day 结果
2013-11-11 3 2013-11-14
2013-11-19 3 2013-11-22
2013-11-13 5 2013-11-20
2013-11-15 1 2013-11-15
------解决方案--------------------
create table #tb(a_date datetime,a_day int)
insert into #tb
select '2013-11-11',3
union all select '2013-11-19',3
union all select '2013-11-13',5
union all select '2013-11-15',1
select a.*,endworkday=DATEADD(day,a.a_day+b.num,a.a_date)
from #tb a
inner join
(select a_date,sum(case when DATEPART(WEEKDAY,lastdate) IN(1,7) then 1 else 0 end) as num
from
(select *,lastdate=DATEADD(DAY,b.number,a_date)
from #tb a,master..spt_values b
where b.type='P' and b.number between 1 and a.a_day
)t
group by a_date
)b on a.a_date=b.a_date
/*
a_date a_day endworkday
-----------------------------------------------------
2013-11-11 00:00:00.000 3 2013-11-14 00:00:00.000
2013-11-19 00:00:00.000 3 2013-11-22 00:00:00.000
2013-11-13 00:00:00.000 5 2013-11-20 00:00:00.000
2013-11-15 00:00:00.000 1 2013-11-17 00:00:00.000
*/
你的答案有问题吧？2013-11-15是周五，一天之后应该是下周一了，所以应该是2013-11-17，没错吧？
------解决方案--------------------
除去周六周日还有法定假日， 如果你要连那个也算进去的话， 估计你得先定义一个法定假日表，然后再操作
------解决方案--------------------
楼主试试这个
create table #tb(a_date datetime,a_day int)
insert into #tb
select '2013-11-11',3
union all select '2013-11-19',3
union all select '2013-11-13',5
union all select '2013-11-15',1
---
select *,case when datepart(dw, dateadd(day, a_day,a_date))=7 then dateadd(day, a_day,a_date+2)
when datepart(dw, dateadd(day, a_day,a_date))=1 then dateadd(day, a_day,a_date+1)
else dateadd(day, a_day,a_date) end
from #tb