扣除节假日高效算法

在日常开发中,有时候需要扣除节假日,本人实际开发中使用了一套比较好的办法与大家讨论.
表结构ER设计如下:


其中:节假日表,是存计算好的节假日结果.并且把日期换成"整形日期",建立索引提高判断速度,
只要节假日,变成,当晚就可以通过DTS最新计算节假日表,
存储过程如下:

CREATE     Procedure  sp_holiday
     @YEAR   int
AS
     -- 产生节假日数据
     -- exec sp_holiday 2005
     SET  NOCOUNT  ON
    
     -- 判断是否需要计算
     IF  ( SELECT  SET_IS_REDO  FROM  TJ_SETTINGS  WHERE  SET_YEAR  =   @YEAR )  =   1
     BEGIN
    
         BEGIN   TRAN
        
         DELETE   FROM  TJ_HOLIDAY  WHERE  HOL_YEAR  =   @YEAR
         DECLARE   @SQL   VARCHAR ( 100 ),  @THIS_DATE   SMALLDATETIME ,  @BEGIN_DATE   SMALLDATETIME ,  @END_DATE   SMALLDATETIME ,  @HOL_NAME   VARCHAR ( 50 ),  @INDEX   TINYINT ,  @IS_WEEK   TINYINT
         -- 初始化双休日数据
         CREATE   TABLE  #WEEK (WEEK_DAY  TINYINT   PRIMARY   KEY   NOT   NULL , IS_WEEK  TINYINT   NULL )
         SET   @INDEX   =   0
         WHILE  ( @INDEX   <   7 )
         BEGIN
             SET   @SQL   =   ' INSERT INTO #WEEK (WEEK_DAY, IS_WEEK) SELECT  '   +   CAST ( @INDEX   AS   CHAR ( 1 ))  +   ' , WK_ '   +   CAST ( @INDEX   AS   CHAR ( 1 ))  +   '  FROM TJ_WEEK WHERE WK_YEAR =  '   +   CAST ( @YEAR   AS   CHAR ( 4 ))
             EXEC ( @SQL )
             SET   @INDEX   =   @INDEX   +   1
         END
         -- 每一天判断
         SET   @BEGIN_DATE   =   CONVERT ( SMALLDATETIME ,  CAST ( @YEAR   AS   CHAR ( 4 ))  +   ' -01-01 ' ,  120 )
         SET   @END_DATE   =   DATEADD ( YEAR ,  1 ,  @BEGIN_DATE )
         SET   @THIS_DATE   =   @BEGIN_DATE
         WHILE  ( @THIS_DATE   <   @END_DATE )
         BEGIN
             -- 非节假日
             IF   EXISTS  ( SELECT   *   FROM  TJ_NONFERIA  WHERE  NFR_YEAR  =   @YEAR   AND  NFR_DATE  =   @THIS_DATE )
             begin
                 SET   @THIS_DATE   =   DATEADD ( DAY ,  1 ,  @THIS_DATE )
                 CONTINUE
             end
             -- 节日
             ELSE   IF   EXISTS  ( SELECT   *   FROM  TJ_FERIA  WHERE  FER_YEAR  =   @YEAR   AND  FER_DATE  =   @THIS_DATE )
             BEGIN
                 SELECT   @HOL_NAME   =  FER_NAME  FROM  TJ_FERIA  WHERE  FER_YEAR  =   @YEAR   AND  FER_DATE  =   @THIS_DATE
                 INSERT   INTO  TJ_HOLIDAY (HOL_YEAR, HOL_DATE_INT, HOL_DATE, HOL_NAME) 
                 VALUES  ( @YEAR ,  FLOOR ( CONVERT ( FLOAT ,  @THIS_DATE )),  @THIS_DATE ,  @HOL_NAME )
             END
             -- 休息日
             ELSE
             BEGIN
                 SELECT   @IS_WEEK   =  IS_WEEK  FROM  #WEEK  WHERE  WEEK_DAY  =  ( DATEPART (WEEKDAY,  @THIS_DATE )  -   1 )
                 IF  ( @IS_WEEK   >   0 )
                     INSERT   INTO  TJ_HOLIDAY (HOL_YEAR, HOL_DATE_INT, HOL_DATE, HOL_NAME) 
                     VALUES  ( @YEAR ,  FLOOR ( CONVERT ( FLOAT ,  @THIS_DATE )),  @THIS_DATE ,  DATENAME (WEEKDAY,  @THIS_DATE ))
             END
             SET   @THIS_DATE   =   DATEADD ( DAY ,  1 ,  @THIS_DATE )
         END
        
         -- 重新设置计算标记
         UPDATE  TJ_SETTINGS  SET  SET_IS_REDO  =   0   WHERE  SET_YEAR  =   @YEAR
        
         IF   @@ERROR   =   0
             COMMIT   TRAN
         ELSE  
             ROLLBACK   TRAN
        
         DROP   TABLE  #WEEK
    
     END
    
     SET  NOCOUNT  OFF

GO

外

/**/ /******************************************************************
* 名称: 工作日重新
* 作者: WANGYJ<edobnet@hotmail.com>
* 时间: 2005-5-17
*
* -----------------------------------------------------------------
* 版本        时间            作者        备注
*
* V1.00        2005-5-3    WANGYJ        创建
* -----------------------------------------------------------------
******************************************************************/
create    Procedure  sp_holiday_ALL
    
AS
     DECLARE  my_cursor  CURSOR   for
     select  SET_YEAR  from  TJ_SETTINGS   where  SET_IS_REDO  =   1
     DECLARE   @year   varchar ( 4 )
    
     open  my_cursor
     FETCH   NEXT   FROM  my_cursor 
     INTO   @year
WHILE   @@FETCH_STATUS   =   0
BEGIN
     exec  risk.sp_holiday  @year
  
     FETCH   NEXT   FROM  my_cursor 
     INTO   @year
       
END
    CLOSE  my_cursor
    DEALLOCATE  my_cursor

GO

节假日扣除函数如下:

CREATE      FUNCTION  risk.CalcDay
(
     @Diff   smallint ,         -- 差别值.正数为加,负数减
     @D_Date   datetime        -- 差别日期
)
RETURNS   datetime
AS
     BEGIN
         declare   @ordDate   datetime
         set   @ordDate   =   @D_Date
         set   @D_Date   =   convert ( char ( 10 ),  @D_Date ,  120 )   -- 去掉时间部分(防止传入的参数中有时间部分,影响处理)
         if   @Diff   >   0
         begin
             while   @Diff   >   0
             begin
                 select   @D_Date   =   @D_Date   +   @Diff ,  @Diff   =   count ( * )  from  TJ_HOLIDAY 
                 where  HOL_DATE_INT  >   FLOOR ( CONVERT ( FLOAT ,  @D_Date ))  AND  HOL_DATE_INT  <=   FLOOR ( CONVERT ( FLOAT ,  @D_Date   +   @Diff ))
             end
         end
         else
         begin
             while   @Diff   <   0
             begin
                 select   @D_Date   =   @D_Date   +   @Diff ,  @Diff   =-   count ( * )  from  TJ_HOLIDAY 
                 where  HOL_DATE_INT  <   FLOOR ( CONVERT ( FLOAT ,  @D_Date ))  AND  HOL_DATE_INT  >=   FLOOR ( CONVERT ( FLOAT ,  @D_Date   +   @Diff ))
             end
         end
     set   @D_Date   =   dateadd (hour, datepart (hour, @ordDate ), @D_Date )
     set   @D_Date   =   dateadd (minute, datepart (minute, @ordDate ), @D_Date )
     set   @D_Date   =   dateadd (second, datepart (second, @ordDate ), @D_Date )
     return ( @D_Date )
     END

这样就可以使用了,dbo.CaclDay(-5,getdate())就是扣除节假以后的5天前的数据

希望对大家有帮助