SQL 计算有效工作时间

SQL计算有效工作时间
说明：_____________________________________________________________ 2
1、目的：______________________________________________________________ 2
2、数据库：____________________________________________________________ 2
3、数据表：____________________________________________________________ 2
4、函数：______________________________________________________________ 2 F_GetWorkTime______________________________________________________ 3 F_GetWorkTimeOnDay_________________________________________________ 5 F_GetWorkTimeOnWeek________________________________________________ 8
说明：
1、目的：
计算有效工作时间，计算时间段内，排除每周休息日、节假（调休）日、每天非工作时间后的有效工作时间
2、数据库：
SQL SERVER
3、数据表：
DayWorkTime :每天工作时间段表 ;
WeekWorkTime :每周工作时间安排表（比如1-5为工作日6-7为休息日）；
VacationDay :节假日（调休日）安排表。

3张表通过wtsid（即同一个工作时间计划表）关联 ,具体表结构请自行设计
4、函数：
F_GetWorkTimeOnDay: 根据设定时间段,计算开始时间和结束时间的有效时
间总量,不排除节假日和周末；
F_GetWorkTimeOnWeek:根据设定时间段,计算开始时间和结束时间的有效时
间总量,不排除节假日; 需要调用F_GetWorkTimeOnDay
F_GetWorkTime:根据设定时间段,计算开始时间和结束时间的有效时间总量，需要调用F_GetWorkTime
F_GetWorkTime
-- =============================================
-- Author: <Author,,HeXiangGui>
-- Create date: <Create Date, ,2018/03/29>
-- Description: <Description, ,根据设定时间段,计算开始时间和结束时间的有效时间总量>
-- @starttime:开始时间
-- @endtime:结束时间
-- =============================================
ALTER FUNCTION [dbo].[F_GetWorkTime](
@startTime DATETIME='2001-01-01',
@endTime DATETIME='2001-01-01',
@wtsid uniqueidentifier='F4C461D4-E2D7-4165-8AE7-CE887B7C3D5E')
RETURNS INT
AS
BEGIN
-----------------------------------
---结束时间早于开始时间直接返回0---
IF(@starttime>=@endtime)
BEGIN
RETURN 0
END
------------------
---临时变量定义---
DECLARE @countTimepart INT --时间段数量
SET @countTimepart=0 --初始化为0
DECLARE @workTime INT
SET @workTime=0
DECLARE @validWorkTime INT
SET @validWorkTime=0
DECLARE @invalidWorkTime INT
SET @invalidWorkTime=0
DECLARE @isvalid BIT
SET @isvalid=0
DECLARE @tempStartTime DATETIME --
DECLARE @tempEndTime DATETIME
------------------------
----全局临时变量赋值----
SELECT @countTimepart=COUNT(1) FROM VacationDay WHERE NOT ((endTime <= @startTime) OR (startTime >= @endTime )) AND wtsid=@wtsid
IF(@countTimepart<1) --时间段和节假日表无交集
BEGIN
SELECT @workTime=dbo.F_GetWorkTimeOnWeek(@startTime,@endTime,@wtsid)
END
IF(@countTimepart>0) --时间段和节假日表有交集
BEGIN
SELECT @tempEndTime=@startTime
WHILE(@countTimepart>0)
BEGIN
SELECT TOP 1 @tempStartTime=CASE WHEN startTime>@startTime THEN startTime ELSE @startTime END,
@tempEndTime=CASE WHEN endTime<@endTime THEN endTime ELSE @endTime
END,@isvalid=isAdjust
FROM VacationDay
WHERE NOT ((endTime <= @tempEndTime) OR (startTime >= @endTime )) AND wtsid=@wtsid ORDER BY startTime
IF(@isvalid=1)
BEGIN
SELECT
@validWorkTime=@validWorkTime+dbo.F_GetWorkTimeOnDay(@tempStartTime,@tempEndTim e,@wtsid)
END
ELSE BEGIN
SELECT
@invalidWorkTime=@invalidWorkTime+dbo.F_GetWorkTimeOnWeek(@tempStartTime,@tempE ndTime,@wtsid)
END
SELECT @countTimepart=@countTimepart-1
END
SELECT
@workTime=dbo.F_GetWorkTimeOnWeek(@startTime,@endTime,@wtsid)-@invalidWorkTime+ @validWorkTime
END
RETURN @workTime
END
F_GetWorkTimeOnDay
-- =============================================
-- Author: <Author,, HeXiangGui >
-- Create date: <Create Date, ,2018/03/29>
-- Description: <Description, ,根据设定时间段,计算开始时间和结束时间的有效时间总量,不排除节假日和周末>
-- @starttime:开始时间
-- @endtime:结束时间
-- =============================================
ALTER FUNCTION [dbo].[F_GetWorkTimeOnDay](
@startTime DATETIME='2001-01-01',
@endTime DATETIME='2001-01-01',
@wtsid uniqueidentifier='F4C461D4-E2D7-4165-8AE7-CE887B7C3D5E')
RETURNS INT
AS
BEGIN
-----------------------------------
---结束时间早于开始时间直接返回0---
IF(@starttime>=@endtime)
BEGIN
RETURN 0
END
------------------
---临时变量定义---
DECLARE @usStartTime INT --开始时间超出第一段开始时间的时长
SET @usStartTime=0
DECLARE @usStartTimePart INT --开始时间超出当前时间段的时长
SET @usStartTimePart=0
DECLARE @usLastTime INT --结束时间时间距离最后一段时间结束还剩下多少时间
SET @usLastTime=0
DECLARE @usLastTimePart INT --结束时间时间距离当前时间段结束还剩下多少时间
SET @usLastTimePart=0
DECLARE @countTimepart INT --时间段数量
SET @countTimepart=0 --初始化为0
DECLARE @partNo INT --第几段时间
SET @partNo=1 --初始为1
DECLARE @startTimePart TIME(0) --时间段开始
DECLARE @endTimePart TIME(0) --时间段结束
DECLARE @countMinute INT --单段时间量
SET @countMinute=0
DECLARE @countDayMinute INT --总时间量（需要计算的所有时间段总和）
SET @countDayMinute=0
------------------------
----全局临时变量赋值----
SELECT @countTimepart=COUNT(1),@countDayMinute=SUM(countMinute) FROM DayWorkTime WHERE wtsid=@wtsid
-------------------------------------------------------------------------
-------------------------@usStartTime计算-------------------------------- SELECT @partNo=1 --时间段序号
SELECT @usStartTime=0 --初始为0
WHILE( @partNo<=@countTimepart) --升序开始遍历时间段
BEGIN
--获取对应时间段的开始、结束、时间段时长（分钟）
SELECT
@endTimePart=endTimePart,@startTimePart=startTimePart,@countMinute=countMinute FROM DayWorkTime
WHERE wtsid=@wtsid AND partNo=@partno
--如开始时间在这个时间段内，则计算时间段起始时间到@starttime的差值并跳出循环结束计算，否则按时间段满值计算
IF(CONVERT(TIME(0), @startTime)<@endTimePart)
BEGIN
SELECT @usStartTimePart=0
SELECT @usStartTimePart=DATEDIFF(MI,@startTimePart,CONVERT(TIME(0), @startTime)) SELECT @usStartTime=@usStartTime+CASE WHEN @usStartTimePart>0 then
@usStartTimePart ELSE 0 END
BREAK
END ELSE
BEGIN
SELECT @usStartTime=@usStartTime+@countMinute
END
SELECT @partNo=@partNo+1
END
-------------------------------------------------------------------------
-------------------------@usLastTime计算--------------------------------- SELECT @partno=@countTimepart
SELECT @usLastTime=0
WHILE(@partno>=1) --降序遍历时间段
BEGIN
--获取对应时间段的开始、结束、时间段时长（分钟）
SELECT
@endTimePart=endTimePart,@startTimePart=startTimePart,@countMinute=countMinute FROM DayWorkTime
WHERE wtsid=@wtsid AND partNo=@partno
IF(CONVERT(TIME(0), @endTime)>@startTimePart) --如结束时间在这个时间段内，则计算时间段结束时间到@endtime的差值并跳出循环结束计算
BEGIN
SELECT @usLastTimePart=0
SELECT @usLastTimePart=DATEDIFF(MI,CONVERT(TIME(0), @endTime),@endTimePart) SELECT @usLastTime=@usLastTime+CASE WHEN @usLastTimePart>0 then @usLastTimePart ELSE 0 END
BREAK
END
ELSE BEGIN
SELECT @usLastTime=@usLastTime+@countMinute
END
SELECT @partno=@partno-1
END
--返回计算结果
RETURN DATEDIFF(DD,@startTime,
@endTime)*@countDayMinute+@countDayMinute-(@usStartTime+@usLastTime)
END
F_GetWorkTimeOnWeek
-- =============================================
-- Author: <Author,, HeXiangGui >
-- Create date: <Create Date, ,2018/03/29>
-- Description: <Description, ,根据设定时间段,计算开始时间和结束时间的有效时间总量,不排除节假日>
-- @starttime:开始时间
-- @endtime:结束时间
-- =============================================
ALTER FUNCTION [dbo].[F_GetWorkTimeOnWeek](
@startTime DATETIME='2001-01-01',
@endTime DATETIME='2001-01-01',
@wtsid uniqueidentifier='F4C461D4-E2D7-4165-8AE7-CE887B7C3D5E')
RETURNS INT
AS
BEGIN
-----------------------------------
---结束时间早于开始时间直接返回0---
IF(@starttime>=@endtime)
BEGIN
RETURN 0
END
------------------
---临时变量定义---
DECLARE @usStartTime INT --开始时间超出第一段开始时间的时长
SET @usStartTime=0
DECLARE @usLastTime INT --结束时间时间距离最后一段时间结束还剩下多少时间
SET @usLastTime=0
DECLARE @partNo INT --第几段时间
SET @partNo=1 --初始为1
DECLARE @countDayMinute INT --日工作总时间量
SET @countDayMinute=0
DECLARE @countDay INT --每周工作天数
SET @countDay=0
DECLARE @startTimeOnWeekDay INT --开始时间是一周的第几天
SET @startTimeOnWeekDay=DATEPART(WEEKDAY,@startTime)
DECLARE @startTimeOfZero DateTime --开始时间所在当天的零点
SET @startTimeOfZero=CONVERT(VARCHAR(10),@startTime)+' 00:00:00'
DECLARE @endTimeOnWeekDay INT --结束时间是一周的第几天
SET @endTimeOnWeekDay=DATEPART(WEEKDAY,@endTime)
DECLARE @endTimeOfZero DateTime --结束时间所在当晚的零点
SET @endTimeOfZero=CONVERT(VARCHAR(10),@endTime)+' 23:59:59'
------------------------
----全局临时变量赋值----
SELECT @countDayMinute=SUM(countMinute) FROM DayWorkTime WHERE wtsid=@wtsid SELECT @countDay=COUNT(1) FROM WeekWorkTime WHERE wtsid=@wtsid AND isWork='True' -------------------------------------------------------------------------
-------------------------@usStartTime计算-------------------------------- SELECT @usStartTime=0
IF(@startTimeOnWeekDay IN (SELECT weekNo FROM WeekWorkTime WHERE wtsid=@wtsid AND isWork='False'))--开始时间是休息日
BEGIN
SELECT @usStartTime=@countDayMinute*COUNT(1)
FROM WeekWorkTime
WHERE wtsid=@wtsid AND isWork='True' AND weekNo<@startTimeOnWeekDay
END
ELSE BEGIN
SELECT
@usStartTime=@countDayMinute*COUNT(1)+dbo.F_GetWorkTimeOnDay(@startTimeOfZero,@ startTime,@wtsid)
FROM WeekWorkTime
WHERE wtsid=@wtsid AND isWork='True' AND weekNo<@startTimeOnWeekDay --开始时间距离每周开始的有效时间
END
-------------------------------------------------------------------------
-------------------------@usLastTime计算--------------------------------- SELECT @usLastTime=0
IF(@endTimeOnWeekDay IN (SELECT weekNo FROM WeekWorkTime WHERE wtsid=@wtsid AND isWork='False'))--结束时间是休息日
BEGIN
SELECT @usLastTime=@countDayMinute*COUNT(1)
FROM WeekWorkTime
WHERE wtsid=@wtsid AND isWork='True' AND weekNo>@endTimeOnWeekDay
END
ELSE BEGIN
SELECT
@usLastTime=@countDayMinute*COUNT(1)+dbo.F_GetWorkTimeOnDay(@endTime,@endTimeOf Zero,@wtsid)
FROM WeekWorkTime
WHERE wtsid=@wtsid AND isWork='True' AND weekNo>@endTimeOnWeekDay --结束时间距离每周结束的有效时间
END
--返回计算结果
RETURN DATEDIFF(WW,@startTime,
@endTime)*@countDay*@countDayMinute+@countDayMinute*@countDay-(@usStartTime+@us LastTime)
END。