34,838
社区成员




两张表,一张施工计划表,有开工日期和工期;另一张表是节假日表,包含所有节假日,只用Sql查询实现计算完工日期,不用函数
看demo,思路很重要。
--结果集(有效完工期表)
declare @result table(
id int identity(1,1) not null,
attachDt datetime
)
--节假日历表
declare @holidays table (
id int identity(1,1) not null,
holiday datetime
)
insert into @holidays
select '2019-10-1' union all
select '2019-10-2' union all
select '2019-10-3' union all
select '2019-10-4' union all
select '2019-10-5' union all
select '2019-10-6' union all
select '2019-10-7'
-- select * from @holidays
----------------------------------------------------------------------------------------
--传参:开工日期及完工日期
declare @beginDate datetime ='2019-9-27',
@workDay int = 21
--计算理论上的预计完工日期
declare @endDate datetime
select @endDate = dateadd(dd,@workDay,@beginDate)
--日期范围拆分
insert into @result
select dateadd(day,number,@BeginDate) from master.dbo.spt_values
where type ='p' and number <= datediff(day,@beginDate,@endDate)
--计算出包含的节假日天数
declare @holiday int
select @holiday = count(*) from @result a inner join @holidays b on a.attachDt = b.holiday
--返回准确的预计完工日期(排除节假日后)
select dateadd(dd,@holiday,max(attachDt)) from @result