sql 查询n 个工作日后的日期
数据库是用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_datea_dayendworkday
-----------------------------------------------------
2013-11-11 00:00:00.00032013-11-14 00:00:00.000
2013-11-19 00:00:00.00032013-11-22 00:00:00.000
2013-11-13 00:00:00.00052013-11-20 00:00:00.000
2013-11-15 00:00:00.00012013-11-17 00:00:00.000
*/
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
