工作日计算

因工作需要，需要编写一个在指定日期后自动加上几天的计算工作日期，需要自动越过假期和周六、周日（上班还不能越过如2014-01-26日）

比如2014-09-25，

加1天就是2014-09-26,

加2天就是2014-09-29，

加3天就是2014-09-30,

加4天就是2014-10-09,

加5天就是2014-10-10，

加6天就是2014-10-13

网上有类似的基本都不能，只能自己写一个

print 'compiling procedure dbo.workday_add ...'

if object_id('dbo.workday_add' ,'p') is not null
    drop procedure dbo.workday_add
go

create procedure dbo.workday_add(
     @rtncode      varchar(5) output
    ,@rtnmsg       varchar(255) output
    ,@begindate    datetime           --开始日期
    ,@workday      int                --要增加的工作日数
    ,@closedate    date output        --截止日期
)
as
/******************************************************************
项目名称：xxxxx管理平台
所属模块：xxxxx
概要说明：
    中文名称：返回截止时间
    用途简述：返回截止时间
语法信息：
    输入参数：
             @begindate  --开始日期
             @workday    --要增加的工作日数
    输出参数：
             @closedate --截止日期
    调用举例：
             declare @rtncode varchar(5),@rtnmsg varchar(255),@closedate date
             exec dbo.workday_add @rtncode output,@rtnmsg output,'2014-09-25',1,@closedate output
             select @rtncode,@rtnmsg,@closedate
修订记录：
    修订日期    修订人     修改内容简要说明
    ----------  ---------  ------------------------------
    2014-09-09  赵文彬     创建
******************************************************************/
begin
    set nocount on
    set datefirst 1
    set @rtncode = '0000'
    set @rtnmsg = 'successful.'

    begin try
        declare @enddate datetime,@dw int
        declare @jia int,@ban int,@work_date datetime
        select @jia=0,@ban=0

        while @workday>0
        begin
            select @dw=datepart(dw, @begindate+1),@enddate=@begindate+1
            if exists(select 1 from dbo.work_time where convert(varchar(10),work_date,101)= convert(varchar(10),@begindate+1,101))
            begin
            select top 1 @jia=case when [type]=1 then 1 else 0 end
                        ,@ban=case when [type]=2 then 1 else 0 end
            from dbo.work_time where convert(varchar(10),work_date,101)= convert(varchar(10),@begindate+1,101)
            end
            else
                begin
                    select @jia=0,@ban=0
                end
            if (@dw=1 and @jia=0) or (@dw=2 and @jia=0) or (@dw=3 and @jia=0) or (@dw=4 and @jia=0) or (@dw=5 and @jia=0) or (@dw=6 and @ban=1) or (@dw=7 and @ban=1)
            begin
                select @begindate=@begindate+1,@workday=@workday-1
            end
            else
                begin
                    select @begindate=@begindate+1
                end
        end
        select @closedate = convert(varchar(10),@enddate,120)
    end try
    begin catch
        select @rtnmsg = error_message()
               ,@rtncode = error_number()
        return
    end catch
end
go

if @@error<>0
    print 'error-----dbo.workday_add-----error'
else
    print 'compiled procedure dbo.workday_add'
go

需要配合工作日历表来使用

/*==============================================================*/
/* Table: WORK_TIME                                             */
/*==============================================================*/
PRINT 'dbo.WORK_TIME'
GO

if object_id('dbo.WORK_TIME', 'U') IS NOT NULL 
   drop table dbo.WORK_TIME

create table dbo.WORK_TIME (
   WORK_DATE            DATE                 not null,
   TYPE                 INT                  not null default 1,
   constraint PK_WORK_TIME primary key (WORK_DATE)
)
go

execute sp_addextendedproperty 'MS_Description', '工作日历', 'schema', 'dbo', 'table', 'WORK_TIME'
go

execute sp_addextendedproperty 'MS_Description', '日期', 'schema', 'dbo', 'table', 'WORK_TIME', 'column', 'WORK_DATE'
go

execute sp_addextendedproperty 'MS_Description', '工作日类型(1-假 2-班)', 'schema', 'dbo', 'table', 'WORK_TIME', 'column', 'TYPE'
go

最后放上2014年的工作日历

truncate table dbo.work_time;

insert into dbo.work_time(work_date,type)
           select '2014-01-01',1
union all select '2014-01-26',2
union all select '2014-01-30',2
union all select '2014-01-31',1
union all select '2014-02-01',1
union all select '2014-02-02',1
union all select '2014-02-03',1
union all select '2014-02-04',1
union all select '2014-02-05',1
union all select '2014-02-06',1
union all select '2014-02-07',2
union all select '2014-02-08',2
union all select '2014-04-05',1
union all select '2014-04-06',1
union all select '2014-04-07',1
union all select '2014-05-01',1
union all select '2014-05-02',1
union all select '2014-05-03',1
union all select '2014-05-04',2
union all select '2014-05-31',1
union all select '2014-06-01',1
union all select '2014-06-02',1
union all select '2014-09-06',1
union all select '2014-09-07',1
union all select '2014-09-08',1
union all select '2014-10-01',1
union all select '2014-10-02',1
union all select '2014-10-03',1
union all select '2014-10-04',1
union all select '2014-10-05',1
union all select '2014-10-06',1
union all select '2014-10-07',1
union all select '2014-10-08',1