去掉周六周日和法定节假日的工作日问题

1.说明：去掉法定节假日（周六，周天）和指定节假日

2.创建表脚本：

代码 
 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.计算两个给定日期之间的实际工作日天数

代码 
 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.计算给定日期加上若干工作日天数

代码 
 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

 

posted @ 2012-10-15 12:16  九极天  阅读(699)  评论(0)  编辑  收藏  举报