去掉周六周日和法定节假日的工作日问题
1.说明:去掉法定节假日(周六,周天)和指定节假日
2.创建表脚本:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE TABLE [dbo].[Holiday] 2 ( 3 ID varchar(36) NOT NULL CONSTRAINT DF_Holiday_ID DEFAULT (newid()), 4 Name varchar(50) NOT NULL, 5 BeginDate date NOT NULL, 6 EndDate date NOT NULL, 7 Flag bit NOT NULL, 8 CONSTRAINT PK_Holiday PRIMARY KEY CLUSTERED (ID) 9 ) 10 GO 11 12 13 EXEC sp_addextendedproperty 'Name','节假日表','user','dbo','table','Holiday' 14 GO 15 16 EXEC sp_addextendedproperty 'MS_Description','ID','user','dbo','table','Holiday','column','ID' 17 GO 18 19 EXEC sp_addextendedproperty 'MS_Description','名称','user','dbo','table','Holiday','column','Name' 20 GO 21 22 EXEC sp_addextendedproperty 'MS_Description','开始时间','user','dbo','table','Holiday','column','BeginDate' 23 GO 24 25 EXEC sp_addextendedproperty 'MS_Description','结束时间','user','dbo','table','Holiday','column','EndDate' 26 GO 27 28 EXEC sp_addextendedproperty 'MS_Description','标识,0表示调班,1表示节假日','user','dbo','table','Holiday','column','Flag' 29 GO
3.计算两个给定日期之间的实际工作日天数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 IF OBJECT_ID (N'[dbo].[fWorkdayDiff]',N'FN') IS NOT NULL 2 DROP FUNCTION [dbo].[fWorkdayDiff] 3 GO 4 5 CREATE FUNCTION [dbo].[fWorkdayDiff](@BeginDate date,@EndDate date) 6 RETURNS int 7 AS 8 BEGIN 9 DECLARE @TempDate date,@Count int,@Flag bit 10 IF @BeginDate>@EndDate 11 SELECT @Flag=1,@TempDate=@BeginDate,@BeginDate=@EndDate,@EndDate=@TempDate 12 ELSE 13 SET @Flag=0 14 SET @Count=0 15 SET @BeginDate=DATEADD(d,1,@BeginDate) 16 WHILE @BeginDate<=@EndDate 17 BEGIN 18 SELECT @Count=CASE WHEN(((@@DATEFIRST+DATEPART(weekday,@BeginDate)-1)%7 BETWEEN 1 AND 5 AND 19 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE Flag=1 AND BeginDate<=@BeginDate AND @BeginDate<=EndDate)=0) OR 20 ((@@DATEFIRST+DATEPART(weekday,@BeginDate)-1)%7 IN (0,6) AND 21 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE Flag=0 AND BeginDate<=@BeginDate AND @BeginDate<=EndDate)>0)) 22 THEN @Count+1 ELSE @Count END,@BeginDate=DATEADD(d,1,@BeginDate) 23 END 24 RETURN (CASE WHEN @Flag=1 THEN -@Count ELSE @Count END) 25 END
4.计算给定日期加上若干工作日天数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 IF OBJECT_ID (N'[dbo].[fWorkdayAdd]',N'FN') IS NOT NULL 2 DROP FUNCTION [dbo].[fWorkdayAdd] 3 GO 4 5 CREATE FUNCTION [dbo].[fWorkdayAdd](@BeginDate date,@Count int) 6 RETURNS date 7 AS 8 BEGIN 9 DECLARE @EndDate date 10 IF @Count>0 11 BEGIN 12 SET @EndDate=DATEADD(d,1,@BeginDate) 13 WHILE @Count>0 14 BEGIN 15 SELECT @Count=CASE WHEN(( 16 (@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 BETWEEN 1 AND 5 AND 17 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=1 AND BeginDate<=@EndDate AND @EndDate<=EndDate)=0) OR 18 ((@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 IN (0,6) AND 19 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=0 AND BeginDate<=@EndDate AND @EndDate<=EndDate)>0)) 20 THEN @Count-1 ELSE @Count END 21 IF @Count=0 22 BEGIN 23 SELECT @EndDate=@EndDate 24 END 25 ELSE 26 BEGIN 27 SELECT @EndDate=DATEADD(d,1,@EndDate) 28 END 29 END 30 END 31 ELSE 32 BEGIN 33 SET @EndDate=DATEADD(d,-1,@BeginDate) 34 WHILE @Count>0 35 BEGIN 36 SELECT @Count=CASE WHEN(( 37 (@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 BETWEEN 1 AND 5 AND 38 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=1 AND BeginDate<=@EndDate AND @EndDate<=EndDate)=0) OR 39 ((@@DATEFIRST+DATEPART(weekday,@EndDate)-1)%7 IN (0,6) AND 40 (SELECT COUNT(*) FROM [dbo].[Holiday] WHERE FLAG=0 AND BeginDate<=@EndDate AND @EndDate<=EndDate)>0)) 41 THEN @Count+1 ELSE @Count END 42 IF @Count=0 43 BEGIN 44 SELECT @EndDate=@EndDate 45 END 46 ELSE 47 BEGIN 48 SELECT @EndDate=DATEADD(d,-1,@EndDate) 49 END 50 END 51 END 52 RETURN @EndDate 53 END
举首望孤星, 低头抚古琴, 高山流水吟不尽,空谷觅知音, 秋风秋水此登临, 秋月秋霜一寸心, 世间只有真情在, 除却巫山不是云, 为有知己万里行, 山水苍茫洗胸襟, 一曲情思随风去,归来化做断弦琴。