sql 两个时间段 重叠时间问题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/black263/article/details/6817395

今天有个问题需要解决,目前还没解决方案呢。

记下此文,以作记录。

问题描述:客户需要对算薪当月的所有加班单进行分析,若加班时间在晚上21:00到凌晨06:00期间全部算作夜班小时数,给予夜班补贴。

有一些比较复杂,比如从下午13点开始加班,加班至凌晨02:00,这中间21:00-02:00的时间算作夜班小时数。

目前还未想到最完美的解决方案。

初步想法是:做一个函数,将加班单号作为参数,最后返回夜班小时数。


比较完整的想法如下:


其中,不同天的结果值应该为:第一天的结果值+中间天的

结果

值+最后一天的结果值。

出于效率考虑,可将同一天的 06:00<开始时间<=21:00 和 结束时间<=21:00 放到前面,因为这种情况较多,且返回值为0,不需要计算。


欢迎交流。

函数如下:



--函数传入3个值,分别是 加班单的开始时间、结束时间、以及加班总时数
ALTER FUNCTION [dbo].[fun_cal_night_time] (@p_sdate datetime,@p_edate datetime)  
RETURNS numeric(18, 4) 
 AS  
BEGIN 
  declare @rtn numeric(18, 4)  --设定变量,返回函数结果值
  declare @rtn1 numeric(18, 4)  --设定变量,第一天
  declare @rtn2 numeric(18, 4)  --设定变量,中间天
  declare @rtn3 numeric(18, 4)  --设定变量,最后一天
  
  --同一天
  if (convert(nvarchar(10),@p_sdate,120)=convert(nvarchar(10),@p_edate,120))
  begin
--开始时间<05:30
if(convert(nvarchar(5),@p_sdate,108)<'05:30')
begin
--结束时间<=05:30,则 结束时间-开始时间
if(convert(nvarchar(5),@p_edate,108)<='05:30')
begin
set @rtn=datediff(mi,@p_sdate,@p_edate)
end
--05:30<结束时间<=21:30,则 05:30-开始时间
else if(convert(nvarchar(5),@p_edate,108)>'05:30' and convert(nvarchar(5),@p_edate,108)<='21:30' )
begin
set @rtn=datediff(mi,@p_sdate,convert(datetime,convert(nvarchar(10),@p_sdate,120)+' 05:30'))
end
-- 结束时间>21:30,则 (05:30-开始时间)+(结束时间-21:30)
else
begin
set @rtn=datediff(mi,@p_sdate,convert(datetime,convert(nvarchar(10),@p_sdate,120)+' 05:30'))+
datediff(mi,convert(datetime,convert(nvarchar(10),@p_edate,120)+' 21:30'),@p_edate)
end
end
--05:30<开始时间<=21:30
else if(convert(nvarchar(5),@p_sdate,108)>'05:30' and convert(nvarchar(5),@p_sdate,108)<='21:30' )
begin
--结束时间<=21:30,则 0
if(convert(nvarchar(5),@p_edate,108)<='21:30')
begin
set @rtn=0
end
--结束时间>21:30,则 (结束时间-21:30)
else
begin
set @rtn=datediff(mi,convert(datetime,convert(nvarchar(10),@p_edate,120)+' 21:30'),@p_edate)
end
end
--开始时间>21:30,任何情况,则结束时间-开始时间
else if(convert(nvarchar(5),@p_sdate,108)>'21:30')
begin
set @rtn=datediff(mi,@p_sdate,@p_edate)
end
  end
  else 
  begin
--第一天
--开始时间<=05:30,则 (00:00-21:30)150+(05:30-开始时间)
if(convert(nvarchar(5),@p_sdate,108)<='05:30')
begin
set @rtn1=150+datediff(mi,@p_sdate,convert(datetime,convert(nvarchar(10),@p_sdate,120)+' 05:30'))
end
--05:30<开始时间<=21:30,则 (00:00-21:30)150
else if(convert(nvarchar(5),@p_sdate,108)>'05:30' and convert(nvarchar(5),@p_sdate,108)<='21:30' )
begin
set @rtn1=150
end
--开始时间>21:30,则 00:00-开始时间
else
begin
set @rtn1=datediff(mi,@p_sdate,dateadd(day,1,convert(datetime,convert(nvarchar(10),@p_sdate,120)+' 00:00')))
end
--中间天
--天数*8天(480min)
set @rtn2= (DATEDIFF(day,@p_sdate,@p_edate)-1)*480
if(@rtn2<0)
begin
set @rtn2=0
end
--最后一天
--结束时间<=05:30,则 结束时间-00:00
if(convert(nvarchar(5),@p_edate,108)<='05:30')
begin
set @rtn3=150+datediff(mi,convert(datetime,convert(nvarchar(10),@p_edate,120)+' 00:00'),@p_edate)
end
--05:30<结束时间<=21:30,则 05:30-00:00 330
else if(convert(nvarchar(5),@p_edate,108)>'05:30' and convert(nvarchar(5),@p_edate,108)<='21:30' )
begin
set @rtn3=330
end
--结束时间>21:30,则 (结束时间-21:30)+(05:30-00:00)330
else
begin
set @rtn3=datediff(mi,convert(nvarchar(10),@p_edate,120)+' 21:30',@p_edate)+330
end
--不同天的则为:第一天+中间天+最后一天
set @rtn=isnull(@rtn1,0)+isnull(@rtn2,0)+isnull(@rtn3,0)
  end
  
  return round(isnull(@rtn,0)/60.0,2)  --返回结果
END

展开阅读全文

没有更多推荐了,返回首页