MRP运算是假期考虑问题,求一高效率"函数"?

Question

ERP中有“假期”表
HLD1(StrDate ,EndDate,Re) 
数据如：
2010-1-1 , 2010-1-3 ，元旦放假
2010-1-15, 2010-1-16, 元宵节放假那么我需要一个函数，给定一个日期，如：2009-12-20，给一个增加的天数，如：20 ，计算除假日外的日期。希望效率最高，谢谢！

Answer 1

create function udf_WeekdayID(@Date datetime)
returns integer
-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1
begin
--1: Monday , ... ,7: Sunday
return (@@Datefirst + datepart(weekday,@Date)) % 7
       + case when (@@Datefirst + datepart(weekday,@Date)) % 7 
gocreate function udf_NextWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的下一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = '2005-01-02'
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
                 then dateadd(day,3,@Date)
            when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                 then dateadd(day,2,@Date)
            else
                 dateadd(day,1,@Date)
       end
end
go

Answer 2

--利用一个临时表加存储过程完成.
create table HLD1(StrDate datetime,EndDate datetime,Re varchar(20)) 
insert into HLD1 values('2010-1-1' , '2010-1-3' ,'元旦放假') 
insert into HLD1 values('2010-1-15', '2010-1-16', '元宵节放假')
create table tb(dt datetime)
go
create proc my_proc @dt datetime , @cnt int
as
begin
  delete from tb
  declare @i as int
  set @i = 0
  while @i <= @cnt - 1
  begin
    if not exists(select 1 from hld1 where @dt + @i between StrDate and EndDate) 
       insert into tb select @dt + @i
    set @i = @i + 1
  end
end
goexec my_proc '2009-12-20' , 20select * from tbdrop table hld1 , tb
drop proc my_proc/*
dt                                                     
------------------------------------------------------ 
2009-12-20 00:00:00.000
2009-12-21 00:00:00.000
2009-12-22 00:00:00.000
2009-12-23 00:00:00.000
2009-12-24 00:00:00.000
2009-12-25 00:00:00.000
2009-12-26 00:00:00.000
2009-12-27 00:00:00.000
2009-12-28 00:00:00.000
2009-12-29 00:00:00.000
2009-12-30 00:00:00.000
2009-12-31 00:00:00.000
2010-01-04 00:00:00.000
2010-01-05 00:00:00.000
2010-01-06 00:00:00.000
2010-01-07 00:00:00.000
2010-01-08 00:00:00.000（所影响的行数为 17 行）*/

Answer 3

--> 测试数据：[HLD1]
if object_id('[HLD1]') is not null drop table [HLD1]
create table [HLD1]([StrDate] varchar(10),[EndDate] varchar(10),[Re] varchar(10))
insert [HLD1]
select '2010-01-01','2010-01-03','元旦放假' union all
select '2010-01-15','2010-01-16','元宵节放假'
--函数
create function func_date(@date varchar(10),@day int)
returns @table table(date varchar(10))
as
begin
insert into @table
select convert(varchar(10),dateadd(day,number,@date),120) as [date]
from master..spt_values
where number < @day and type='P'
and convert(varchar(10),dateadd(day,number,@date),120) not in
(
select convert(varchar(10),dateadd(day,r.number,t.StrDate),120) as [date]
from master..spt_values r,HLD1 t 
where r.type='P'
and dateadd(day,r.number,t.StrDate)<=t.EndDate
)
return
end
--调用
select * from dbo.func_date('2009-12-20',20)
--结果
--------------------------------
2009-12-20
2009-12-21
2009-12-22
2009-12-23
2009-12-24
2009-12-25
2009-12-26
2009-12-27
2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08

Answer 4

--输入天数有限制，master..spt_values不够用的话自己建个临时表代替

Answer 5

最近怎么问这个问题的人很多？标准的做法是。建立一个 wkCalendar 表 (wkdate smalldatetime primary key, wkType)  wkType 0 工作日 , 7周末, 9 法定假日
然后把一年的数据都准备好。剩下就简单了。select count(*) from wkCalendar  where wkdate between '2009-12-20' and '2009-12-20'+20

Answer 6

我现在的做法和楼上差不多，在ERP标准软件中定义的数据结构和我前面写的一样，但我经过转换，通过语句：
  SELECT DISTINCT dateadd(d,T1.AbsEntry -1 , T0.StrDate) FeastDay   FROM HLD1 T0 
     JOIN OFPR T1 ON DATEADD(d,T1.AbsEntry-1,T0.StrDate) <= T0.EndDate
   WHERE hldCode = (select hldCode FROM OADM)
仅仅将“假日”数据放到中间表中，对日期建立聚集索引。
后面采用函数：
alter function dbo.DateAddbyDay1
(
@StartDate datetime,
@CoverFeast char(1),
@AddDay int
)
RETURNS DATETIME with ENCRYPTION AS 
BEGIN
   DECLARE @EndDate DATETIME  , @FeastDayQty INT
   IF  (ISNULL(@AddDay,0)<=0)
   BEGIN
     SET @EndDate = DATEADD(d,isnull(@AddDay,0),@StartDate)
   END
   ELSE
   BEGIN    
     SET @EndDate = DATEADD(d,ISNULL(@AddDay,0) ,@StartDate)
     SELECT @FeastDayQty = ISNULL(COUNT(1),0) FROM [U_OHLD] WHERE FeastDay BETWEEN @StartDate AND @EndDate
     SET @EndDate = DATEADD(d,@FeastDayQty,@EndDate)
   END
   RETURN @EndDate
END
实现日期计算，发现现在的效率还可以的。