/*
有效工作时间计算
用户账号
工作时间：星期一，星期二，星期三，星期四，星期五
星期一(08:00-12:00,13:00-17:30)
星期二(08:00-12:00,13:00-17:30)
星期三(08:00-12:00,13:00-17:30)
星期四(08:00-12:00,13:00-17:30)
星期五(08:00-12:00,13:00-17:30)
法定节假日：
10-01(0)
请假/出差/加班：03-10(1加班，2请假/出差，13:00-17:30)
---------算法一--------
是常规工作日
是请假
{...}
否
{...}
否
是加班
{...}
否
{...}
---------算法二--------
1.常规工作日时间计算有效时间
2.增加加班有效时间（排除常规工作日时间）
3.减去请假/出差无效时间（排除常规工作日时间）
---------算法三：区间合并法---------
-:非工作时段
*:工作时段
@:休假
区间描述说明
区间包含：
|-----------|*******************|------------|
|--------------|@@@@@@@@@@@|-----------------|
区间被包含:
|-----------|*******************|------------|
|--------|@@@@@@@@@@@@@@@@@@@@@@@@@@|--------|
区间重叠偏左:
|-----------|*******************|------------|
|------|@@@@@@@@@@@@@@|----------------------|
区间重叠偏右：
|-----------|*******************|------------|
|--------------------|@@@@@@@@@@@@@@@|-------|
区间外：
|---|************|---------------------------|
|--------------------|@@@@@@@@@@@|-----------|
|------------------------------------------------------------------------|
1 5
a: |--------|*******************|----------|**********************|---------|
0 3
b: |************|------------|*********************|------------------------|
2
c: |---------------|**************************|-----------------------------|
4
d: |---------------------------------|**************************************|
StartTime排序
0
b: |************|-----------------------------------------------------------|
a: |------------------|*********|-------------------------------------------|
c: |------------------------|@@@@@@@@@@@@@@@@@|-----------------------------|
d: |---------------------------------------------------|********************|
数据结构设计：
------------------------------------------------------------------------------------------------
TWorkOfWeek 周工作时间（七条固定记录，从1到7分别表示星期一到星期天）
int ID 自动编号
int WeekDay 1-7:星期一到星期天
int RecessFullDay 是否全天休假(1:全休)
THoriday 公共特殊日期（假期，调休）
int ID 自动编号
datetime SDate 日期（不含时间部分或者时分秒为“00:00:00”）
int RecessFullDay 是否全天休假(1:全休)
TSpecialDay 个人特殊日工作/非工作日：请假/休假/加班/年假
int ID 自动编号
varchar(16) LoginName 账号名称
datetime SDate 日期（不含时间部分或者时分秒为“00:00:00”）
int RecessFullDay 是否全天休假(1:全休)
TWorkOfPeriodTime 天工作时间段
int ID 自动编号
int TimeKind 时间段类型:0或空-特殊日,1-工作日，2-公共假期
int DayID 日期ID(特殊日,工作日，公共假期)
int IsWork 是否工作(1:是工作)
int WorkKind 工作类型:工作日/法定假日/请假/休假/加班/年假
int StartHour 开始时间-时
int StartMinute 开始时间-分
int EndHour 结束时间-时
int EndMinute 结束时间-分
------------------------------------------------------------------------------------------------
*/
--if exists(select * from sysobjects where type='FN' and name='fn_CaculateWorkTimeValid')
--drop function fn_CacualteWorkTimeValid
--go
--use manageproblem
--go
--SET DATEFIRST 1 --星期一为第一天
--declare @LoginName varchar(30)
--declare @StartTime datetime
--declare @EndTime datetime
--select @LoginName = '3002'
--select @StartTime = '2007-03-09 11:59:10'
--select @EndTime = '2007-03-09 13:00:11'
--185 2007-03-01 16:29:19.930 2007-03-02 16:13:41.693 254
--create function fn_CaculateWorkTimeValid(
CREATE function fn_CaculateWorkTimeValid(
@LoginName varchar(30),
@StartTime datetime ,
@EndTime datetime
)
returns int
as
begin
/*
--初始化 TWorkOfWeek
declare TWTWorkOfWeek table (
[ID] int, --自动编号
[WeekDay] int, --0-6:星期一到星期天
[RecessFullDay] bit --是否全天休假(1:全休)
)
--初始化 THoriday
declare TWTHoriday table (
[ID] int, --自动编号
[SDate] datetime, --月份（不含时间部分或者时分秒为“00:00:00”）
[RecessFullDay] bit --是否全天休假(1:全休)
)
--初始化 TSpecialDay
declare TWTSpecialDay table (
[ID] int, --自动编号
[LoginName] varchar(16), --账号名称
[SDate] datetime, --日期（不含时间部分或者时分秒为“00:00:00”）
[RecessFullDay] bit --是否全天休假(1:全休)
)
--初始化 TWorkOfPeriodTime
declare TWTWorkOfPeriodTime table (
[ID] int, --自动编号
[TimeKind] int, --时间段类型:0或空-特殊日,1-工作日，2-公共假期
[DayID] int, --日期ID(特殊日,工作日，公共假期)
[IsWork] bit, --是否工作(1:是工作)
[WorkKind] int, --工作类型:工作日/法定假日/请假/休假/加班/年假
[StartHour] int, --开始时间-时
[StartMinute] int, --开始时间-分
[EndHour] int, --结束时间-时
[EndMinute] int --结束时间-分
)
*/
--初始化有效时间计算的时间区间
declare @TWorkOfPeriodTimeArea table (
SDate datetime,
StartHour int,
StartMinute int,
EndHour int,
EndMinute int
)
------ 测试数据 实例账号 amdin -------
/*
--工作日设置TWorkOfWeek
insert into TWTWorkOfWeek values(1,1,0) --工作日 星期一
insert into TWTWorkOfWeek values(2,2,0) --工作日
insert into TWTWorkOfWeek values(3,3,0) --工作日
insert into TWTWorkOfWeek values(4,4,0) --工作日
insert into TWTWorkOfWeek values(5,5,0) --工作日
insert into TWTWorkOfWeek values(6,6,1) --非工作日
insert into TWTWorkOfWeek values(7,7,1) --非工作日
--公共假日设置THoriday
insert into TWTHoriday values(1,'2007-03-05',1) --公共假期，全休
insert into TWTHoriday values(2,'2007-03-07',0) --公共假期，半休
--特殊日设置TSpecialDay
insert into TWTSpecialDay values(1,'admin','2007-03-08',1) --个人请假，全休
insert into TWTSpecialDay values(2,'admin','2007-03-09',0) --个人请假，半休
insert into TWTSpecialDay values(3,'admin','2007-03-10',0) --个人加班
--时间段设置TWorkOfPeriodTime
--工作日
insert into TWTWorkOfPeriodTime values(01,1,1,1,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(02,1,1,1,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(03,1,2,1,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(04,1,2,1,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(05,1,3,1,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(06,1,3,1,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(07,1,4,1,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(08,1,4,1,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(09,1,5,1,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(10,1,5,1,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(11,1,6,0,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(12,1,6,0,1,13,00,17,30)
insert into TWTWorkOfPeriodTime values(13,1,7,0,1,08,30,12,00)
insert into TWTWorkOfPeriodTime values(14,1,7,0,1,13,00,17,30)
--公共假期
insert into TWTWorkOfPeriodTime values(15,2,1,0,2,08,30,12,00) --星期四,3.8，公共假期放半天假
insert into TWTWorkOfPeriodTime values(16,2,1,0,2,13,00,17,30) --星期四,3.8，公共假期放半天假
insert into TWTWorkOfPeriodTime values(17,2,2,0,2,13,00,17,30) --星期四,3.8，公共假期放半天假
--特殊日
insert into TWTWorkOfPeriodTime values(18,0,1,0,5,08,00,12,00) --个人请假,全休
insert into TWTWorkOfPeriodTime values(19,0,1,0,5,13,00,17,30) --个人请假,全休
insert into TWTWorkOfPeriodTime values(20,0,2,0,5,08,30,12,00) --个人请假
insert into TWTWorkOfPeriodTime values(21,0,3,1,3,08,30,12,00) --个人加班
*/
declare @returnVal nvarchar(1000)
select @ReturnVal = ''
--------- 有效时间计算 ------------
declare @nAllValidWorkTime int --有效工作时间（分钟）
declare @nValidWorkTime int --工作日时间累加
declare @nValidHoridayTime int --公共特殊日期工作时间累加
declare @nInValidHoridayTime int --公共特殊日期非工作时间累加
declare @nValidSpecialTime int --个人特殊日期工作时间累加
declare @nInVaidSpecialTime int --个人特殊日期非工作时间累加
declare @i int --循环计数器
declare @tStartTimeSplit datetime --时间分离开始时间
declare @tEndTimeSplit datetime --时间分离结束时间
declare @nWeekDay int --当前日期的星期几
declare @ID int
declare @Date datetime --日期（不含时间部分）
--时间分离
declare @nDayDif int --开始时间 和 结束时间 间隔天数
select @nDayDif = datediff(day,@StartTime,@EndTime)
select @i = 0
while(@i<)
begin
--分析个日期的时间段
if(@i=0)
begin
select @tStartTimeSplit = @StartTime
select @tEndTimeSplit = convert(varchar(5),year(@StartTime)) + '-' + convert(varchar(5),month(@StartTime)) + '-' + convert(varchar(5),day(@StartTime)) + ' 23:59'
end
else
begin
if(@i=@nDayDif)
begin
select @tStartTimeSplit = convert(varchar(5),year(@EndTime)) + '-' + convert(varchar(5),month(@EndTime)) + '-' + convert(varchar(5),day(@EndTime))
select @tEndTimeSplit = @EndTime
end
else
begin
select @tStartTimeSplit = dateadd(day,@i,@StartTime)
select @tStartTimeSplit = convert(varchar(5),year(@tStartTimeSplit)) + '-' + convert(varchar(5),month(@tStartTimeSplit)) + '-' + convert(varchar(5),day(@tStartTimeSplit))
select @tEndTimeSplit = convert(varchar(5),year(@tStartTimeSplit)) + '-' + convert(varchar(5),month(@tStartTimeSplit)) + '-' + convert(varchar(5),day(@tStartTimeSplit)) + ' 23:59'
end
end
declare @IsAreaBaoHan bit --区间包含
declare @IsAreaBBaoHan bit --区间被包含
declare @IsAreaLeft bit --区间重叠偏左
declare @IsAreaRight bit --区间重叠偏右
declare @IsAreaOut bit --区间外
select @Date = convert(varchar(5),year(@tStartTimeSplit)) + '-' + convert(varchar(5),month(@tStartTimeSplit)) + '-' + convert(varchar(5),day(@tStartTimeSplit))
--初始化各时间技术参数
select @nValidWorkTime = 0 --工作日时间累加
select @nValidHoridayTime = 0 --公共特殊日期工作时间累加
select @nInValidHoridayTime = 0 --公共特殊日期非工作时间累加
select @nValidSpecialTime = 0 --个人特殊日期工作时间累加
select @nInVaidSpecialTime = 0 --个人特殊日期非工作时间累加
--当前日期为星期几
偏移量,校正(@@DATEFIRST-1)+datepart(weekday,'2007-3-20')
--取模((@@DATEFIRST-1)+datepart(weekday,'2007-3-20')) % 7
--select @nWeekDay = DATEPART(weekday,@Date)
select @nWeekDay = ((@@DATEFIRST-1)+datepart(weekday,@Date)) % 7
--游标数据集存储变量
declare @CurID int --自动编号
declare @CurTimeKind int --时间段类型:0或空-特殊日,1-工作日，2-公共假期
declare @CurDayID int --日期ID(特殊日,工作日，公共假期)
declare @CurIsWork int --是否工作(1:是工作)
declare @CurWorkKind int --工作类型:工作日/法定假日/请假/休假/加班/年假
declare @CurStartHour int --开始时间-时
declare @CurStartMinute int --开始时间-分
declare @CurEndHour int --结束时间-时
declare @CurEndMinute int --结束时间-分
--debug--
/*
SELECT ID,TimeKind,DayID,IsWork,WorkKind,StartHour,StartMinute,EndHour,EndMinute
FROM TWTWorkOfPeriodTime
select @Date
select @nWeekDay
select * from TWTWorkOfWeek where and RecessFullDay<>1
select * from TWTHoriday where and RecessFullDay<>1
select * from TWTSpecialDay where and and RecessFullDay<>1
SELECT ID,TimeKind,DayID,IsWork,WorkKind,StartHour,StartMinute,EndHour,EndMinute
FROM TWTWorkOfPeriodTime
where ((TimeKind=1 and DayID in(select ID from TWTWorkOfWeek where and RecessFullDay<>1)) --工作日
or (TimeKind=2 and DayID in(select ID from TWTHoriday where and RecessFullDay<>1)) --公共特殊日期（假日，调休）
or (TimeKind=0 and DayID in(select ID from TWTSpecialDay where and and RecessFullDay<>1)) ----个人特殊日期（请假，加班...）
) and IsWork=1
--/debug--
*/
--工作时间游标
DECLARE GetTWorkOfPeriodTime CURSOR FOR
SELECT ID,TimeKind,DayID,IsWork,WorkKind,StartHour,StartMinute,EndHour,EndMinute
FROM TWTWorkOfPeriodTime
where ((TimeKind=1 and DayID in(select ID from TWTWorkOfWeek where and RecessFullDay<>1)) --工作日
or (TimeKind=2 and DayID in(select ID from TWTHoriday where and RecessFullDay<>1)) --公共特殊日期（假日，调休）
or (TimeKind=0 and DayID in(select ID from TWTSpecialDay where and and RecessFullDay<>1)) ----个人特殊日期（请假，加班...）
) and IsWork=1
OPEN GetTWorkOfPeriodTime
FETCH NEXT FROM GetTWorkOfPeriodTime
INTO
@CurID, --自动编号
@CurTimeKind, --时间段类型:0或空-特殊日,1-工作日，2-公共假期
@CurDayID, --日期ID(特殊日,工作日，公共假期)
@CurIsWork, --是否工作(1:是工作)
@CurWorkKind, --工作类型:工作日/法定假日/请假/休假/加班/年假
@CurStartHour, --开始时间-时
@CurStartMinute,--开始时间-分
@CurEndHour, --结束时间-时
@CurEndMinute --结束时间-分
WHILE @@FETCH_STATUS = 0
BEGIN
select @IsAreaBaoHan = 0
select @IsAreaBBaoHan = 0
select @IsAreaLeft = 0
select @IsAreaRight = 0
select @IsAreaOut = 0
--区间包含，不添加记录
--debug--
/*
select '---------------------------'
select 'Date:' + convert(varchar(20),@Date)
select * from @TWorkOfPeriodTimeArea
*/
--/debug--
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour<@CurStartHour) or ( and StartMinute<))
and
((EndHour>@CurEndHour) or ( and EndMinute>=@CurEndMinute))
and
)
begin
select @IsAreaBaoHan = 1
--debug--
/*
select '区间包含'
select * from @TWorkOfPeriodTimeArea
where ((StartHour<@CurStartHour) or ( and StartMinute<))
and
((EndHour>@CurEndHour) or ( and EndMinute>=@CurEndMinute))
and
select convert(varchar(2),@CurStartHour) + ':' + convert(varchar(2),@CurStartMinute)
select convert(varchar(2),@CurEndHour) + ':' + convert(varchar(2),@CurEndMinute)
*/
--/debug--
end
--区间被包含，删除区间内的记录，然后添加新记录
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>=@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<))
and
)
begin
--debug--
--select '区间被包含'
--/debug--
select @IsAreaBBaoHan = 1
--删除区间内的记录
delete from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>=@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<))
and
--添加新记录
insert into @TWorkOfPeriodTimeArea values(@Date,@CurStartHour,@CurStartMinute,@CurEndHour,@CurEndMinute)
end
--区间重叠偏左，合并区间(更新StartHour,StartMinute,EndHour,EndMinute)
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>@CurStartMinute))
and
((Starthour<@CurEndHour) or ( and StartMinute<@CurEndMinute))
and
)
begin
--debug--
--select '区间重叠偏左'
--/debug--
select @IsAreaLeft = 1
update @TWorkOfPeriodTimeArea Set
where ((StartHour>@CurStartHour) or ( and StartMinute>@CurStartMinute))
and
((Starthour<@CurEndHour) or ( and StartMinute<@CurEndMinute))
and
end
--区间重叠偏右，合并区间后添加新记录，把旧记录删除
if exists(select * from @TWorkOfPeriodTimeArea
where ((EndHour>@CurStartHour) or ( and EndMinute>@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<@CurEndMinute))
and
)
begin
--debug--
--select '区间重叠偏右'
--/debug--
select @IsAreaRight = 1
update @TWorkOfPeriodTimeArea Set
where ((EndHour>@CurStartHour) or ( and EndMinute>@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<@CurEndMinute))
and
end
--区间外
if not(@IsAreaBaoHan=1 or @IsAreaBBaoHan=1 or @IsAreaLeft=1 or @IsAreaRight=1)
select @IsAreaOut=1
if(@IsAreaOut=1)
begin
--debug--
--select 'out'
--/debug--
--添加新记录
insert into @TWorkOfPeriodTimeArea values(@Date,@CurStartHour,@CurStartMinute,@CurEndHour,@CurEndMinute)
end
FETCH NEXT FROM GetTWorkOfPeriodTime
INTO
@CurID, --自动编号
@CurTimeKind, --时间段类型:0或空-特殊日,1-工作日，2-公共假期
@CurDayID, --日期ID(特殊日,工作日，公共假期)
@CurIsWork, --是否工作(1:是工作)
@CurWorkKind, --工作类型:工作日/法定假日/请假/休假/加班/年假
@CurStartHour, --开始时间-时
@CurStartMinute,--开始时间-分
@CurEndHour, --结束时间-时
@CurEndMinute --结束时间-分
END
CLOSE GetTWorkOfPeriodTime
DEALLOCATE GetTWorkOfPeriodTime
--Debug--
--select * from TWTWorkOfPeriodTime
--select * from @TWorkOfPeriodTimeArea
--/Debug--
--非工作时间游标
DECLARE GetTNotWorkOfPeriodTime CURSOR FOR
SELECT ID,TimeKind,DayID,IsWork,WorkKind,StartHour,StartMinute,EndHour,EndMinute
FROM TWTWorkOfPeriodTime
where ((TimeKind=2 and DayID in(select ID from TWTHoriday where )) --公共特殊日期（假日，调休）
or (TimeKind=0 and DayID in(select ID from TWTSpecialDay where and )) ----个人特殊日期（请假，加班...）
) and IsWork<>1
OPEN GetTNotWorkOfPeriodTime
FETCH NEXT FROM GetTNotWorkOfPeriodTime
INTO
@CurID, --自动编号
@CurTimeKind, --时间段类型:0或空-特殊日,1-工作日，2-公共假期
@CurDayID, --日期ID(特殊日,工作日，公共假期)
@CurIsWork, --是否工作(1:是工作)
@CurWorkKind, --工作类型:工作日/法定假日/请假/休假/加班/年假
@CurStartHour, --开始时间-时
@CurStartMinute,--开始时间-分
@CurEndHour, --结束时间-时
@CurEndMinute --结束时间-分
WHILE @@FETCH_STATUS = 0
BEGIN
--在区间外，删除时间段
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>=@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<))
and
)
begin
--删除区间内的记录
delete from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>=@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<))
and
end
--在区间内，分割记录
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour<@CurStartHour) or ( and StartMinute<))
and
((EndHour>@CurEndHour) or ( and EndMinute>=@CurEndMinute))
and
)
begin
declare @StartHourArea int
declare @StartMinuteArea int
declare @EndHourArea int
declare @EndMinuteArea int
DECLARE GetTWorkOfPeriodTimeArea CURSOR FOR
SELECT StartHour,StartMinute,EndHour,EndMinute
FROM @TWorkOfPeriodTimeArea
where ((StartHour<@CurStartHour) or ( and StartMinute<))
and
((EndHour>@CurEndHour) or ( and EndMinute>=@CurEndMinute))
and
OPEN GetTWorkOfPeriodTimeArea
FETCH NEXT FROM GetTWorkOfPeriodTimeArea
INTO @StartHourArea ,@StartMinuteArea,@EndHourArea,@EndMinuteArea
CLOSE GetTWorkOfPeriodTimeArea
DEALLOCATE GetTWorkOfPeriodTimeArea
--删除记录
delete FROM @TWorkOfPeriodTimeArea
where ((StartHour<@CurStartHour) or ( and StartMinute<))
and
((EndHour>@CurEndHour) or ( and EndMinute>=@CurEndMinute))
and
--左边记录
if ((@StartHourArea<@CurStartHour) or (@StartHourArea=@CurStartHour and @StartMinuteArea<@CurStartMinute))
begin
insert into @TWorkOfPeriodTimeArea values(@Date,@StartHourArea,@StartMinuteArea,@CurStartHour,@CurStartMinute)
end
--右边记录
if ((@EndHourArea>@CurEndHour) or (@EndHourArea=@CurEndHour and @EndMinuteArea>@CurEndMinute))
begin
insert into @TWorkOfPeriodTimeArea values(@Date,@CurEndHour,@CurEndMinute,@EndHourArea,@EndMinuteArea)
end
end
--区间重叠偏左，合并区间(更新StartHour,StartMinute,EndHour,EndMinute)
if exists(select * from @TWorkOfPeriodTimeArea
where ((StartHour>@CurStartHour) or ( and StartMinute>@CurStartMinute))
and
((Starthour<@CurEndHour) or ( and StartMinute<@CurEndMinute))
and
)
begin
update @TWorkOfPeriodTimeArea Set
where ((StartHour>@CurStartHour) or ( and StartMinute>@CurStartMinute))
and
((Starthour<@CurEndHour) or ( and StartMinute<@CurEndMinute))
and
end
else
--区间重叠偏右，合并区间(更新StartHour,StartMinute,EndHour,EndMinute)
if exists(select * from @TWorkOfPeriodTimeArea
where ((EndHour>@CurStartHour) or ( and EndMinute>@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<@CurEndMinute))
and
)
begin
update @TWorkOfPeriodTimeArea Set
where ((EndHour>@CurStartHour) or ( and EndMinute>@CurStartMinute))
and
((EndHour<@CurEndHour) or ( and EndMinute<@CurEndMinute))
and
end
FETCH NEXT FROM GetTNotWorkOfPeriodTime
INTO
@CurID, --自动编号
@CurTimeKind, --时间段类型:0或空-特殊日,1-工作日，2-公共假期
@CurDayID, --日期ID(特殊日,工作日，公共假期)
@CurIsWork, --是否工作(1:是工作)
@CurWorkKind, --工作类型:工作日/法定假日/请假/休假/加班/年假
@CurStartHour, --开始时间-时
@CurStartMinute,--开始时间-分
@CurEndHour, --结束时间-时
@CurEndMinute --结束时间-分
END
CLOSE GetTNotWorkOfPeriodTime
DEALLOCATE GetTNotWorkOfPeriodTime
--1.常规工作日时间计算有效时间
--2.增加加班有效时间（排除常规工作日时间）
--3.减去请假/出差无效时间（排除常规工作日时间）
select @i=@i + 1
end
select @nValidWorkTime = (select sum(datediff(minute,
convert(varchar(2),StartHour) + ':' + convert(varchar(2),StartMinute),
convert(varchar(2),EndHour) + ':' + convert(varchar(2),EndMinute)
)
) as ValidWorkTime
from @TWorkOfPeriodTimeArea
)
--提取开始时间、结束时间的时、分
declare @StartHour int
declare @StartMinute int
declare @EndHour int
declare @EndMinute int
select @StartHour = datepart(hour,@StartTime)
select @StartMinute = datepart(minute,@StartTime)
select @EndHour = datepart(Hour,@EndTime)
select @EndMinute = datepart(Minute,@EndTime)
declare @MinDateTime datetime --最小时间
declare @MaxDateTime datetime --最大时间
--计算区间包含/偏移交叉的最小时间
select @MinDateTime = (select min(convert(datetime,convert(varchar(5),year(SDate)) + '-' + convert(varchar(5),month(SDate)) + '-' + convert(varchar(5),day(SDate)) + ' '
+ convert(varchar(2),StartHour) + ':' + convert(varchar(2),StartMinute))) as MinDateTime
from @TWorkOfPeriodTimeArea
where ((@StartHour>StartHour) or ((@StartHour=StartHour) and @StartMinute>=StartMinute))
and
((@StartHour )
--计算区间包含/偏移交叉的最大时间
select @MaxDateTime = (select max(convert(datetime,convert(varchar(5),year(SDate)) + '-' + convert(varchar(5),month(SDate)) + '-' + convert(varchar(5),day(SDate)) + ' '
+ convert(varchar(2),EndHour) + ':' + convert(varchar(2),EndMinute))) as MaxDateTime
from @TWorkOfPeriodTimeArea
where ((@EndHour>StartHour) or ((@EndHour=StartHour) and @EndMinute>=StartMinute))
and
((@EndHour )
--纠正计算数据,截取头尾部分时间
declare @MinDateTimeDif int --最小时间 和 开始时间 差
declare @MaxDateTimeDif int --最大时间 和 结束时间 差
declare @nAreaOutMinuteDifCount int --区间外的时间总数
--计算区间外的时间总数
select @nAreaOutMinuteDifCount = (select sum(datediff(minute,convert(nvarchar(2),StartHour) + ':' + convert(nvarchar(2),StartMinute)
,convert(nvarchar(2),EndHour) + ':' + convert(nvarchar(2),EndMinute)
)) as nMinuteDifCount
from @TWorkOfPeriodTimeArea
where (
((EndHour<@StartHour) or (() and EndMinute<@StartMinute))
and SDate=convert(nvarchar(5),year(@StartTime)) + '-' + convert(nvarchar(5),month(@StartTime)) + '-' + convert(nvarchar(5),day(@StartTime)) --区间外时间（开始时间时间之日）
)
or
(
((StartHour>@EndHour) or (() and StartMinute>@EndMinute))
and SDate=convert(nvarchar(5),year(@EndTime)) + '-' + convert(nvarchar(5),month(@EndTime)) + '-' + convert(nvarchar(5),day(@EndTime)) --区间外时间（结束时间之日）
)
)
select @nAreaOutMinuteDifCount = (select isnull(@nAreaOutMinuteDifCount,0) as c) --如果为空，设零
if @StartTime>@MinDateTime --开始时间大于最小时间,减去开始部分多余的无效时间
select @nValidWorkTime = @nValidWorkTime - datediff(minute,@MinDateTime,@StartTime)
if @EndTime<@MaxDateTime --结束时间小于最大时间,减去结束部分多余的无效时间
select @nValidWorkTime = @nValidWorkTime - datediff(minute,@EndTime,@MaxDateTime)
select @nValidWorkTime = @nValidWorkTime - @nAreaOutMinuteDifCount --减去区间外的时间
--Debug--
--select * from TWTWorkOfPeriodTime
/*
select * from @TWorkOfPeriodTimeArea
select @MinDateTime
select @StartTime
select @EndTime
select @MaxDateTime
select @returnVal = convert(varchar(100),@nValidWorkTime)
select @returnVal
*/
--/Debug--
select @nValidWorkTime = (select isnull(@nValidWorkTime,0) as c)
-- select @nValidWorkTime
return @nValidWorkTime
end
