日期:2014-05-17 浏览次数:20727 次
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
*/