碰到这么一个问题：
起始日期是2015-09-01， 周期为10天，这个周期为正常工作时间 需要从一张节假日维护表里在这个范围的日期进行排除。 得到最后的时间。
例：节假日维护表数据：
Date
2015-09-03
2015-09-04
2015-09-05
2015-09-12
2015-09-13
得到的结束日期为：2015-09-15
我需要得到最终的结束日期。
求指教下。
------解决思路----------------------
DECLARE @time DATETIME
SET @time='2015-09-01'
SELECT Da,WE FROM (
SELECT
DATEADD(DAY,number,@time)AS Da,datename(WEEKDAY,DATEADD(DAY,number,@time))AS WE
,ROW_NUMBER()OVER(ORDER BY GETDATE())ID FROM MASTER..spt_values WHERE TYPE='p' and number BETWEEN 0 AND 15
and datename(WEEKDAY,DATEADD(DAY,number,@time))NOT IN ('星期六','星期日')
)a
WHERE a.ID=11
------解决思路----------------------
--不知道理解的对不对
select dd from
(select dateadd(dd,number,'2015-9-1') as dd,rn=row_number() over (order by dateadd(dd,number,'2015-9-1'))
from master..spt_values
where type='P' and number>='0' and number<30 and dateadd(dd,number,'2015-9-1') not in (select date from 节假日维护表)) a
where rn=10
------解决思路----------------------
declare @t table(
Date date
)
insert @t
select '2015-09-03'
union all
select '2015-09-04'
union all
select '2015-09-05'
union all
select '2015-09-12'
union all
select '2015-09-13'
union all
select '2015-09-16'
union all
select '2015-09-23'
union all
select '2015-09-24'
declare @startDate date='2015-09-01'
declare @cnt int=10
declare @endDate date=cast(dateadd(DAY,@cnt-1,@startDate) as date)
while(exists(select 1 from @t where Date between @startDate and @endDate))
begin
select @cnt=COUNT(distinct date) from @t where Date between @startDate and @endDate
select @startDate=max(date) from @t where Date between @startDate and @endDate
set @startDate=cast(dateadd(DAY,1,@startDate) as date)
set @endDate=cast(dateadd(DAY,@cnt,@endDate) as date)
--print '----'
--print @cnt
--print @startDate
--print @endDate
end
select @endDate
------解决思路----------------------
DECLARE @s DATETIME='2015-09-01'
DECLARE @t TABLE(d DATETIME);
INSERT INTO @t SELECT @s;
--SELECT DATEADD(dd,b.number,a.d) FROM @t a JOIN master..spt_values b ON 1=1 AND b.type='p' AND b.number<10
;WITH cte AS (
SELECT '2015-09-03' AS d UNION ALL
SELECT '2015-09-04' UNION ALL
select '2015-09-05' UNION ALL
select '2015-09-12' UNION ALL
select '2015-09-13'
)
SELECT TOP 10 * FROM cte u FULL JOIN
(
SELECT DATEADD(dd,b.number,a.d) AS dd FROM @t a JOIN master..spt_values b ON 1=1 AND b.type='p' AND b.number<20
)v ON u.d=v.dd WHERE u.d IS NULL
选出了上班天数，取最大为15号。