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

[exinke]

ERP中有“假期”表
HLD1(StrDate ,EndDate,Remark)
数据如：
2010-1-1 , 2010-1-3 ，元旦放假
2010-1-15, 2010-1-16, 元宵节放假

那么我需要一个函数，给定一个日期，如：2009-12-20，给一个增加的天数，如：20 ，计算除假日外的日期。

希望效率最高，谢谢！

[jinjazzli]

帮顶

[exinke]

没高手出招？

[nalnait]

这有什么特殊的吗

[exinke]

[Quote=引用 3 楼 nalnait 的回复:]
这有什么特殊的吗
[/Quote]
楼上可否给个"函数"SQL？

[--小F--]

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 
go

create 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

[dawugui]

--利用一个临时表加存储过程完成.

create table HLD1(StrDate datetime,EndDate datetime,Remark 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
go

exec my_proc '2009-12-20' , 20

select * from tb

drop 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 行）

*/

[nianran520]

--> 测试数据：[HLD1]
if object_id('[HLD1]') is not null drop table [HLD1]
create table [HLD1]([StrDate] varchar(10),[EndDate] varchar(10),[Remark] 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

[nianran520]

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

[ACMAIN_CHM]

最近怎么问这个问题的人很多？

标准的做法是。

建立一个 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

[exinke]

我现在的做法和楼上差不多，在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
实现日期计算，发现现在的效率还可以的。

[exinke]

我在文件中已经写到“高效率”，按照楼上几位的做法,效率可能有些低；不知道我的理解是否正确。