项目中眼下已有一周表存储了一年中全部的假日,给定查询起始日期和结束日期,推导出查询时间段内工作日是多少。为了简化这个问题,须要以下几个如果。
1. 不考虑周六周日,将其视作普通工作日
2. 假日没有交叠情况。不会出现10月1日到7日是国庆节,当中又有一个其他的节日
给出假日表的设计,某个假日都有起始时间和结束时间。这里仅仅取月日,这样就能表示每一年的假日。
CREATE TABLE [dbo].[holiday]( [begin_time] [varchar](50) NULL, [end_time] [varchar](50) NULL ) ON [PRIMARY] GO
插入測试数据,比如插入国庆节的假日
给定查询时间段为从2014-09-30至2014-10-08,这期间的工作日
declare @query_begin datetime --查询起始时间 declare @query_end datetime --查询结束时间 declare @year1 int declare @year2 int declare @yeartemp int declare @total_holidays int set @query_begin = '2014-09-01' set @query_end = '2015-01-31' set @year1 = YEAR(@query_begin) set @year2 = YEAR(@query_end) --存储全部的含有年月日的假期 IF object_id('tempdb..#temp') is not null BEGIN drop table #temp END CREATE table #temp ( begin_time date, end_time date, ) insert into #temp select convert(varchar(4),@year1)+'-'+begin_time, convert(varchar(4),@year1)+'-'+end_time from holiday --这里主要考虑查询时间段跨年的情况 set @yeartemp=@year1+1 while @yeartemp<=@year2 begin insert into #temp select convert(varchar(4),@yeartemp)+'-'+begin_time, convert(varchar(4),@yeartemp)+'-'+end_time from holiday set @yeartemp=@yeartemp+1 end --去掉和查询时间段没有一点交集的假日段 delete from #temp where end_time<@query_begin or begin_time>@query_end select @total_holidays= SUM(DATEDIFF(dd,begin_time,end_time)+1) from ( select case when begin_time<@query_begin then @query_begin else begin_time end as begin_time, case when end_time>@query_end then @query_end else end_time end as end_time from #temp ) t select DATEDIFF(DD,@query_begin,@query_end)+1-@total_holidays drop table #temp
运行该脚本就能够得到结果是2。符合预期。
以下给出一些特殊測试用例。验证脚本能否正确计算工作日。
1. 查询时间为2014-10-05至2014-10-08
结果:1
2. 查询时间为2014-09-30至2014-10-07
结果:1
3. 添加一条假日,比如是教师节,查询时间段为2014-09-01至2014-10-08
结果:30
4. 在添加一条假日记录,元旦。查询时间段为2014-09-01至2015-01-31
如今holiday表的记录为:
假设手动去算就是:30+31+30+31+31-7-1-1=144
实际结果:144