计算时间间隔求助

Question

create table 业务表(
pk varchar(10) primary key,
userid varchar(10) not null,
starttime datetime not null,
endtime datetime
);create table 假期表(
pk int primary key,
userid varchar(10) not null,
starttime datetime not null,
endtime datetime not null
)业务表存放业务数据,
假期表存放节假日、年假、病假(不包括周六周日),不包括本来就是周六周日的 节假日现在要求每条业务数据从开始到结束的扣除假期表记录,扣除周六周日的小时数，
业务表中endtime没有的话取当前时间,
假期表中如果一条数据不是整天的话 小于4小时 按 实际小时数计算,超过4小时按24小时计算
周六、周日 按24小时计算
最后结果不是整小时的话用去尾法DB是ms sqlserver 2000
如果要用到 视图、存储过程、函数、自定义类型 都可以,触发器不可以用谢谢,觉得有点难度 :(

Answer 1

老大的，计算工作时间的
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_worktime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_worktime]
GO--定义工作时间表
CREATE TABLE tb_worktime(
ID       int identity(1,1) PRIMARY KEY,            --序号
time_start smalldatetime,                            --工作的开始时间
time_end  smalldatetime,                           --工作的结束时间
worktime  AS DATEDIFF(Minute,time_start,time_end)  --工作时数(分钟)
)
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkTime]
GO--计算两个日期之间的工作时间
CREATE FUNCTION f_WorkTime(
@date_begin datetime,  --计算的开始时间
@date_end datetime     --计算的结束时间
)RETURNS int
AS
BEGIN
DECLARE @worktime int
IF DATEDIFF(Day,@date_begin,@date_end)=0
SELECT @worktime=SUM(DATEDIFF(Minute,
CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
THEN CONVERT(VARCHAR,@date_begin,108)
ELSE time_start END,
CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end
THEN CONVERT(VARCHAR,@date_end,108)
ELSE time_end END))
FROM tb_worktime 
WHERE time_end>CONVERT(VARCHAR,@date_begin,108)
AND time_start<CONVERT(VARCHAR,@date_end,108)
ELSE
SET @worktime
=(SELECT SUM(CASE
WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end)
ELSE worktime END)
FROM tb_worktime 
WHERE time_end>CONVERT(VARCHAR,@date_begin,108))
+(SELECT SUM(CASE 
WHEN CONVERT(VARCHAR,@date_end,108)<time_end
THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108))
ELSE worktime END)
FROM tb_worktime 
WHERE time_start<CONVERT(VARCHAR,@date_end,108))
+CASE 
WHEN DATEDIFF(Day,@date_begin,@date_end)>1 
THEN (DATEDIFF(Day,@date_begin,@date_end)-1)
*(SELECT SUM(worktime) FROM tb_worktime)
ELSE 0 END
RETURN(@worktime)
END

Answer 2

没测试create proc p_date
@starttime datetime,@endtime datetime
as
begin
SET LANGUAGE us_english
declare @i int
declare @ss int
set @i=0
while @i<datediff(dd,isnull(@endtime,getdate()),@starttime)
set @starttime=dateadd(dd,@i,@starttime)
if datename(weekday,@starttime)='Saturday' or datename(weekday,@starttime)='Sunday' 
set @ss=@ss+24
set @i=@i+1select datediff(Hour,isnull(y.endtime,getdate()),y.starttime) - @ss - 
(select sum(case when datediff(Hour,j.endtime,j.starttime)>=4 then 24 else datediff(Hour,j.endtime,j.starttime) end )
 from 假期表 j where j.uiserid=y.userid)
from 业务表 y
end

Answer 3

--工作日处理函数(标准节假日)
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