扣除节假日高效算法

在日常开发中,有时候需要扣除节假日,本人实际开发中使用了一套比较好的办法与大家讨论.
表结构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(SMALLDATETIMECAST(@YEAR AS CHAR(4)) + '-01-01'120)
        
SET @END_DATE = DATEADD(YEAR1@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(DAY1@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 (@YEARFLOOR(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 (@YEARFLOOR(CONVERT(FLOAT@THIS_DATE)), @THIS_DATEDATENAME(WEEKDAY, @THIS_DATE))
            
END
            
SET @THIS_DATE = DATEADD(DAY1@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_Date120)  --去掉时间部分(防止传入的参数中有时间部分,影响处理)
        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天前的数据

希望对大家有帮助

posted on 2005-08-21 18:01  edobnet  阅读(5691)  评论(9编辑  收藏  举报