一个疯掉的需求

Question

现有数据库一表分配时间                           实际工时     完成时间
2009-12-15 09：00：00     2            2009-12-15 16：00：00现在要利用 分配时间 和 预计工时 算出 任务是否提前完成 即 是否小于完成时间
有张表记录节假日情况日期        描述     是否休假
2010-01-01  元旦     是另外公司是8：00 --- 17：00  上班 午休12：00-13：00  周六日休息也就是说 如果下午5点分配的工作，预计工时3小时， 如果是正常工作日 完成时间在第二天11点前，
都算提前完成工作现在求一函数  
function GetPlanTiem(分配时间，预计工时)
{
考虑节假日，
遇到节假日和下班，时间顺延，Return 计划完成时间
}博客园有人提出个方案 http://space.cnblogs.com/question/11091/
但感觉不完善，请高人登场

Answer 1

如果不算时间,比较好办.参考如下:
工作日处理函数(标准节假日)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime    --计算的结束日期
)RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE 
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO/*=================================================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--增加整周的天数
SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
--增加不是整周的工作天数
WHILE @workday<>0 
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
THEN @workday-@bz ELSE @workday END
--避免处理后的日期停留在非工作日上
WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6) 
SET @date=DATEADD(Day,@bz,@date)
RETURN(@date)
END工作日处理函数(自定义节假日)
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null)             --假日名称
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO--计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime   --计算的结束日期
)RETURNS int
AS
BEGIN
IF @dt_begin>@dt_end
RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_begin AND @dt_end))
RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_end AND @dt_begin)))
END
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
IF @workday>0
WHILE @workday>0
SELECT @date=@date+@workday,@workday=count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
ELSE
WHILE @workday<0
SELECT @date=@date+@workday,@workday=-count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
RETURN(@date)
END
如果你还要考虑时间,就有点麻烦了.

Answer 2

--还有几个情况得考虑
--> 测试数据：[vacation]
--假日表
if object_id('[vacation]') is not null drop table [vacation]
create table [vacation]([日期] varchar(10),[描述] varchar(4),[是否休假] varchar(2))
insert [vacation]
select '2010-01-01','元旦','是'--select * from [vacation]declare @AllocationTime datetime,-- 分配时间
@hour int--预计工时select @AllocationTime = '2009-12-15 09:00:00',@hour = 50declare @day int,--天数
@weekend int, --周末天数
@vacationday int --法定假日天数select @day = ceiling(@hour*1.0/8)declare @PlanTime datetime --计划完成时间select 
datepart(weekday,dateadd(day,number,@AllocationTime)) as [type],
convert(varchar(10),dateadd(day,number,@AllocationTime),120) as [date]
into #temp
from master..spt_values
where type = 'P' and number<@day
--周末天数
select @weekend = count(1) from #temp where [type] in (1,7)
--假日天数
select @vacationday = count(1) from #temp
where [date] in (select [日期] from [vacation] where [是否休假] = '是')
--实际天数
select @day = @day + @weekend + @vacationdayselect @PlanTime = dateadd(day,@day-1,@AllocationTime)
--剩余小时数
select @hour =  @day%8select @PlanTime = dateadd(hour,@hour,@PlanTime)select @PlanTimedrop table #temp
-----------------
2009-12-23 10:00:00.000

Answer 3

--任务表
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
GO
--节日表
IF OBJECT_ID('t2') IS NOT NULL
DROP TABLE t2
GO
--8：00 --- 17：00  上班 午休12：00-13：00 周六日休息 CREATE TABLE t1(id iNT IDENTITY,d1 DATETIME, span INT , d2 DATETIME)
GO
INSERT t1 SELECT '2009-12-15 09:00:00',2,'2009-12-15 16:00:00'
UNION ALL SELECT '2009-09-30 15:00:00',7,'2009-10-14 09:00:00'
GO
CREATE TABLE t2(d DATETIME,s VARCHAR(20))
GO
INSERT t2 SELECT '2009-10-01','国庆'
INSERT t2 SELECT '2009-10-02','国庆'
INSERT t2 SELECT '2009-10-03','国庆'
INSERT t2 SELECT '2009-01-01','元旦'
GOSELECT id,d1,d2,
CASE WHEN span <
--发布任务起至下班所余工作小时   a
DATEDIFF(hh,d1,DATEADD(dd,1,CONVERT(VARCHAR(10),d1,120))) - CASE WHEN DATEPART(hh,d1)<12 THEN 8 ELSE 7 END
 + 
--上班至结束任务前使用工作小时   b
DATEPART(hh,d2) - CASE WHEN DATEPART(hh,d2)>=12 THEN 9 ELSE 8 END
+ 

8 * 
(
--d2,d1之天数差
CASE WHEN DATEDIFF(dd,d1,d2) - 1 <0 THEN 0 ELSE DATEDIFF(dd,d1,d2)-1 END
-
(
--d2,d1间周末的天数
((DATEDIFF(dd,d1,DATEADD(dd,-(DATEPART(dw,d2)-1) + 1,d2)) + 5 )/7 ) * 2 
+ 
--d2,d1间节日的天数
COUNT(d)
- 
--可能节日与周末有重合，则去掉重复计算的部分
SUM(CASE WHEN DATEPART(dw,d) IN (1,7) THEN 1 ELSE 0 END)
)
)  --*8得到d1,d2间间隔天数的工作小时  c,  则 a+b+c为使用之总小时数
THEN '超时'
ELSE '正常'
END FROM t1 a
LEFT JOIN t2 b
ON d BETWEEN d1 AND d2
GROUP BY id,d1,d2,span

Answer 4

--假日表
if object_id('[vacation]') is not null drop table [vacation]
create table [vacation]([日期] varchar(10),[描述] varchar(4),[是否休假] varchar(2))
insert [vacation]
select '2010-01-01','元旦','是'--select * from [vacation]declare @AllocationTime datetime,-- 分配时间
@hour int--预计工时select @AllocationTime = '2009-12-15 13:00:00',@hour = 7declare @day int,--天数
@weekend int, --周末天数
@vacationday int --法定假日天数select @day = ceiling(@hour*1.0/8)declare @PlanTime datetime --计划完成时间select 
datepart(weekday,dateadd(day,number,@AllocationTime)) as [type],
convert(varchar(10),dateadd(day,number,@AllocationTime),120) as [date]
into #temp
from master..spt_values
where type = 'P' and number<@day--周末天数
select @weekend = count(1) from #temp where [type] in (1,7)
--假日天数
select @vacationday = count(1) from #temp
where [date] in (select [日期] from [vacation] where [是否休假] = '是')
and [type] not in (1,7)--实际天数
select @day = @day + @weekend + @vacationdayselect @PlanTime = dateadd(day,@day-1,@AllocationTime)
--剩余小时数
select @hour =  case when @hour <= 8 then @hour else @hour%8 endselect @PlanTime = dateadd(hour,@hour,@PlanTime)select @PlanTime = case when convert(varchar(2),@AllocationTime,108)<='12'
and convert(varchar(2),@AllocationTime,108)>='13'
then dateadd(hour,1,@PlanTime) else @PlanTime end
select @PlanTime = case when convert(varchar(2),@PlanTime,108)>'17'
then dateadd(hour,cast(convert(varchar(2),@PlanTime,108) as int) + 8 - 17,
dateadd(day,1,convert(varchar(10),@PlanTime)))
else @PlanTime endselect @PlanTimedrop table #temp