SQL计算时间差时如何排除非工作时间

weixin_37722207 2017-12-05 12:30:45
我要计算b.rDateTime与a.rDateTime之间的时间差，但想排除非工作时间和周末，例如：a.rDateTime等于2017-12-04 17:00:00.000，b.rDateTime等于2017-12-05 09:00:00.000，排除下班时间18:00-第二天09:00，即两个日期的时间差只是1个小时，请问SQL可以实现吗？请大神们来帮忙，谢谢！

select
r.id,
r.rNumber,
r.rTopics,
r.rContent,
r.rMemo,
r.rCreateDate,
r.rEndDate,
r.rRStatus,
r.rTaskPName,
a.Actions as shenhe,
a.rDateTime as shenhetime,
b.actions as shouli,
b.rDateTime as shoulitime,
(convert(varchar, b.rDateTime-a.rDateTime,108)) balance
from tbTaskRD r left join (select *,Row_Number() OVER (partition by rNumber ORDER BY rDateTime desc) seq from tbActions where Actions like ('(审批岗位)%') ) a on r.rNumber=a.rNumber
left join (select *,Row_Number() OVER (partition by rNumber ORDER BY rDateTime desc) seq from tbActions where Actions like('(受理岗位)%')) b on r.rNumber=b.rNumber
where r.rMethod='' and r.rCreateDate>('2017-01-01 00:00:00.000') and r.rRStatus='完成' and r.rTopics like ('%Support%')
...全文
1463 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
weixin_37722207 2017-12-06
  • 打赏
  • 举报
 回复
引用 2 楼 qq_37170555 的回复:

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	shenhetime DATETIME,
	shoulitime DATETIME	
)
INSERT INTO #tab
SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL
SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL
SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'
--测试数据结束

--创建函数获取两个日期间的间隔天数(除去周六日)
alter FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	DECLARE @cnt INT
	SET @cnt=0
	SET @begintime=DATEADD(DAY,1,@begintime)
	WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
	BEGIN
		IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
		BEGIN
			SET @cnt=@cnt
		END			
		ELSE 
		BEGIN
			SET @cnt=@cnt+1
		END			
		SET @begintime=DATEADD(DAY,1,@begintime)	
	END
	INSERT INTO @tab
	SELECT @cnt
	RETURN
END

--调用函数计算(前三个小时数相加就是你要的数据了)
SELECT a.*, 
		CASE 
            WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0
            ELSE 18 -DATENAME(hour, shenhetime)
       END                            AS [审核当天小时数当天周末的话就为0)],
        CASE 
            WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0
            ELSE DATENAME(hour, shoulitime) -9
        END                           AS [受理当天总小时数(当天周末的话就为0)],        
        b.cnt*8 AS 间隔小时数,		--一天八小时计算 
		b.cnt AS 间隔天数                              		      
FROM #tab A
OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b
大致就是这样了，你可以试试
大侠，能再帮忙看看吗？我昨天已经附上了我做的小修改和运行的结果，在线等
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 20 楼 weixin_37722207 的回复:
再改一下这个函数，还得加两个判断，像下面这样，应该就没多大问题了，你再看看 

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	insert into @tab
	SELECT  CASE
			--开始结束同一天且不是周末
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) AND DATEPART(weekday, @begintime)NOT  IN (7, 1)  
				THEN
					CASE  
							--判断工作时间
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 AND DATENAME(hour, @endtime)>18 then 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 AND DATENAME(hour, @endtime)<=18 THEN DATEDIFF(hour,@begintime,@endtime)
							WHEN DATENAME(hour, @begintime)<9  AND DATENAME(hour, @endtime)>18 THEN 9
							WHEN DATENAME(hour, @begintime)<9  AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime)-9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
					END
			--开始结束同一天且是周末					
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) AND DATEPART(weekday, @begintime)  IN (7, 1) THEN 0  										  
			--开始/结束日期为空
			WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
			--开始结束非同一天
			ELSE 				
				CASE 
					--开始时间为周末
					WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
					ELSE 
						--开始时间为非周末
						CASE  
							--判断开始时间当天小时数
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)<9 THEN 9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
						END
				END                           
				+ 
				CASE 
					--结束时间为周末
					WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
					ELSE 
						--结束时间为非周末
						CASE  
							--判断结束时间当天小时数
							when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
							WHEN DATENAME(hour, @endtime)<9 THEN 0
							WHEN DATENAME(hour, @endtime)>18 THEN 9
						END                	
				END
			 END                                 
		  +  b.cnt*9  as      hours                                             
	FROM  dbo.getCnt(@begintime,@endtime) b
	RETURN
END
weixin_37722207 2017-12-06
  • 打赏
  • 举报
 回复
引用 19 楼 qq_37170555 的回复:
[quote=引用 17 楼 weixin_37722207 的回复:] 刚刚的图片居然变成了表情，再发一次图片

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	insert into @tab
	SELECT  CASE
			--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
			--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
			WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
			ELSE 
				CASE 
					WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
					ELSE 
						CASE  
							--判断开始时间
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)<9 THEN 9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
						END
				END                           
				+ 
				CASE 
					WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
					ELSE 
						CASE  
							--判断结束时间
							when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
							WHEN DATENAME(hour, @endtime)<9 THEN 0
							WHEN DATENAME(hour, @endtime)>18 THEN 9
						END                	
				END
			 END                                 
		  +  b.cnt*9  as      hours                                             
	FROM  dbo.getCnt(@begintime,@endtime) b
	RETURN
END
 
 
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
        INSERT INTO @tab
        SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END 
    INSERT INTO @tab
    SELECT @cnt   
    RETURN
END
上面那个代码有个5和6还没改过来，这个代码应该改掉了[/quote] 大神，太谢谢你了，大部分能跑出来了，就是还有一个小问题，就是shenhetime如果不在工作时间内，‘2017-01-06 00:04:36.057’与‘2017-01-06 09:15:50.197’应该是小于1小时，但计出来9小时了
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 17 楼 weixin_37722207 的回复:
刚刚的图片居然变成了表情，再发一次图片

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	insert into @tab
	SELECT  CASE
			--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
			--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
			WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
			ELSE 
				CASE 
					WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
					ELSE 
						CASE  
							--判断开始时间
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)<9 THEN 9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
						END
				END                           
				+ 
				CASE 
					WHEN DATEPART(weekday, @endtime) IN (7, 1) THEN 0
					ELSE 
						CASE  
							--判断结束时间
							when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
							WHEN DATENAME(hour, @endtime)<9 THEN 0
							WHEN DATENAME(hour, @endtime)>18 THEN 9
						END                	
				END
			 END                                 
		  +  b.cnt*9  as      hours                                             
	FROM  dbo.getCnt(@begintime,@endtime) b
	RETURN
END
 
 
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
        INSERT INTO @tab
        SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END 
    INSERT INTO @tab
    SELECT @cnt   
    RETURN
END
上面那个代码有个5和6还没改过来，这个代码应该改掉了
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 17 楼 weixin_37722207 的回复:
刚刚的图片居然变成了表情，再发一次图片

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	insert into @tab
	SELECT  CASE
			--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
			--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
			WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
			ELSE 
				CASE 
					WHEN DATEPART(weekday, @begintime) IN (7, 1) THEN 0
					ELSE 
						CASE  
							--判断开始时间
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)<9 THEN 9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
						END
				END                           
				+ 
				CASE 
					WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
					ELSE 
						CASE  
							--判断结束时间
							when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
							WHEN DATENAME(hour, @endtime)<9 THEN 0
							WHEN DATENAME(hour, @endtime)>18 THEN 9
						END                	
				END
			 END                                 
		  +  b.cnt*9  as      hours                                             
	FROM  dbo.getCnt(@begintime,@endtime) b
	RETURN
END
 
 
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
        INSERT INTO @tab
        SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=7 OR DATEPART(weekday,@begintime)=1)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END 
    INSERT INTO @tab
    SELECT @cnt   
    RETURN
END
之前搞错了，礼拜六和礼拜天应该是对应7和1，我以为是5和6，所以出来有问题，你再用上面的代码试试。应该没问题了，不过你最好多测试几个不同的时间段看看。 有什么问题再说
weixin_37722207 2017-12-06
  • 打赏
  • 举报
 回复
刚刚的图片居然变成了表情，再发一次图片
weixin_37722207 2017-12-06
  • 打赏
  • 举报
 回复
引用 15 楼 qq_37170555 的回复:
[quote=引用 13 楼 weixin_37722207 的回复:] 修改完函数，但不知为什么没数据出来
看上面那个，之前的有点小问题。还有一个方面没有考虑到[/quote] 有计算出来，但好像不正确，'2017-12-01 17:00:00.000','2017-12-04 12:00:00.000'这两个时间之间应该是相差4个工作小时才对啊，但出来的结果是21 不好意思，又得麻烦大神帮忙看看
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 13 楼 weixin_37722207 的回复:
修改完函数，但不知为什么没数据出来
看上面那个，之前的有点小问题。还有一个方面没有考虑到
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复 

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	insert into @tab
	SELECT  CASE
			--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
			WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
			--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
			WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
			ELSE 
				CASE 
					WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
					ELSE 
						CASE  
							--判断开始时间
							when DATENAME(hour, @begintime)>=9 AND DATENAME(hour, @begintime)<=18 THEN 18 -DATENAME(hour, @begintime)
							WHEN DATENAME(hour, @begintime)<9 THEN 9
							WHEN DATENAME(hour, @begintime)>18 THEN 0
						END
				END                           
				+ 
				CASE 
					WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
					ELSE 
						CASE  
							--判断结束时间
							when DATENAME(hour, @endtime)>=9 AND DATENAME(hour, @endtime)<=18 THEN DATENAME(hour, @endtime) -9
							WHEN DATENAME(hour, @endtime)<9 THEN 0
							WHEN DATENAME(hour, @endtime)>18 THEN 9
						END                	
				END
			 END                                 
		  +  b.cnt*9  as      hours                                             
	FROM  dbo.getCnt(@begintime,@endtime) b
	RETURN
END
 
 
ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
        INSERT INTO @tab
        SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END 
    INSERT INTO @tab
    SELECT @cnt   
    RETURN
END
一天按9个小时计算的，9点到18点之间的都算是工作时间，其他时间为非工作时间
weixin_37722207 2017-12-06
  • 打赏
  • 举报
 回复
引用 12 楼 qq_37170555 的回复:
[quote=引用 9 楼 weixin_37722207 的回复:] 大侠，能再帮忙看看吗？我昨天已经附上了我做的小修改和运行的结果，在线等
最终版本，能考虑的问题都替你考虑进去了，你看看还有什么地方有问题 

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
insert into @tab
SELECT  CASE
		--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
		WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
		--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
		WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
		ELSE 
            CASE 
                WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
                ELSE 18 -DATENAME(hour, @begintime)
           END                           
           + CASE 
                WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
                ELSE DATENAME(hour, @endtime) -9
             END
         end                                 
      +  b.cnt*8  as      hours                                             
FROM  dbo.getCnt(@begintime,@endtime) b
 RETURN
END


ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
    	INSERT INTO @tab
		SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END    
    RETURN
END
[/quote] 修改完函数，但不知为什么没数据出来
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 9 楼 weixin_37722207 的回复:
大侠，能再帮忙看看吗？我昨天已经附上了我做的小修改和运行的结果，在线等
最终版本，能考虑的问题都替你考虑进去了，你看看还有什么地方有问题 

ALTER FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
insert into @tab
SELECT  CASE
		--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)  
		WHEN CAST(@begintime AS date)=cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) 
		--新增一个判断开始结束日期是否有一个为空，如果是则间隔时间为0
		WHEN ISNULL(@begintime,'')='' OR ISNULL(@endtime,'')='' THEN 0
		ELSE 
            CASE 
                WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
                ELSE 18 -DATENAME(hour, @begintime)
           END                           
           + CASE 
                WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
                ELSE DATENAME(hour, @endtime) -9
             END
         end                                 
      +  b.cnt*8  as      hours                                             
FROM  dbo.getCnt(@begintime,@endtime) b
 RETURN
END


ALTER FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
    DECLARE @cnt INT
    SET @cnt=0
    --新增判断是否开始结束日期为空的
    IF (isnull(@begintime,'')='' OR ISNULL(@endtime,'')='')
    BEGIN
    	INSERT INTO @tab
		SELECT @cnt
    END
    ELSE
    SET @begintime=DATEADD(DAY,1,@begintime)
    WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
    BEGIN
        IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
        BEGIN
            SET @cnt=@cnt
        END           
        ELSE 
        BEGIN
            SET @cnt=@cnt+1
        END           
        SET @begintime=DATEADD(DAY,1,@begintime)   
    END    
    RETURN
END
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 9 楼 weixin_37722207 的回复:
[quote=引用 2 楼 qq_37170555 的回复:] 

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	shenhetime DATETIME,
	shoulitime DATETIME	
)
INSERT INTO #tab
SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL
SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL
SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'
--测试数据结束

--创建函数获取两个日期间的间隔天数(除去周六日)
alter FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	DECLARE @cnt INT
	SET @cnt=0
	SET @begintime=DATEADD(DAY,1,@begintime)
	WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
	BEGIN
		IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
		BEGIN
			SET @cnt=@cnt
		END			
		ELSE 
		BEGIN
			SET @cnt=@cnt+1
		END			
		SET @begintime=DATEADD(DAY,1,@begintime)	
	END
	INSERT INTO @tab
	SELECT @cnt
	RETURN
END

--调用函数计算(前三个小时数相加就是你要的数据了)
SELECT a.*, 
		CASE 
            WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0
            ELSE 18 -DATENAME(hour, shenhetime)
       END                            AS [审核当天小时数当天周末的话就为0)],
        CASE 
            WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0
            ELSE DATENAME(hour, shoulitime) -9
        END                           AS [受理当天总小时数(当天周末的话就为0)],        
        b.cnt*8 AS 间隔小时数,		--一天八小时计算 
		b.cnt AS 间隔天数                              		      
FROM #tab A
OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b
大致就是这样了，你可以试试
大侠，能再帮忙看看吗？我昨天已经附上了我做的小修改和运行的结果，在线等[/quote] 看上面那个回复，昨天给你改好了，可能有点事忘了发出去了
听雨停了 2017-12-06
  • 打赏
  • 举报
 回复
引用 8 楼 weixin_37722207 的回复:
执行完结果成这样子了

Create FUNCTION gethours(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
insert into @tab
--新增一个判断开始结束日期是否同一天，如果同一天就直接DATEDIFF(hour,@begintime,@endtime)
SELECT  CASE CAST(@begintime AS date) WHEN cast(@endtime AS DATE) THEN DATEDIFF(hour,@begintime,@endtime) ELSE 
			CASE 
				WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0
				ELSE 18 -DATENAME(hour, @begintime)
		   END                           
		   + CASE 
				WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0
				ELSE DATENAME(hour, @endtime) -9
			 END
         end                                 
      +  b.cnt*8  as      hours                                             
FROM  dbo.getCnt(@begintime,@endtime) b
 RETURN
END
把你的gethours改成这样再试试
weixin_37722207 2017-12-05
  • 打赏
  • 举报
 回复
执行完结果成这样子了
weixin_37722207 2017-12-05
  • 打赏
  • 举报
 回复
引用 6 楼 qq_37170555 的回复:
[quote=引用 4 楼 weixin_37722207 的回复:] 我执行了，然后计算出来的时间好像不太对，
你这gethours是不是用我写的getcnt改了，这个getcnt是用来获取两个日期中除去周末的间隔天数，我这边执行出来的就是0天啊，你怎么搞出个9来了，我也是醉了[/quote] 因为我要计算到具体相差多少个小时，所以我在创建函数getcnt的时候参考你提供的，加了下面的语句，得出来了一个gethousr的函数，但不知为什么得出来的就不正确了。 Create FUNCTION gethours( @begintime DATETIME, @endtime DATETIME ) RETURNS @tab TABLE(cnt int) AS BEGIN insert into @tab SELECT CASE WHEN DATEPART(weekday, @begintime) IN (5, 6) THEN 0 ELSE 18 -DATENAME(hour, @begintime) END + CASE WHEN DATEPART(weekday, @endtime) IN (5, 6) THEN 0 ELSE DATENAME(hour, @endtime) -9 END + b.cnt*8 as hours FROM dbo.getCnt(@begintime,@endtime) b RETURN END
听雨停了 2017-12-05
  • 打赏
  • 举报
 回复
引用 4 楼 weixin_37722207 的回复:
我执行了，然后计算出来的时间好像不太对，


你这gethours是不是用我写的getcnt改了，这个getcnt是用来获取两个日期中除去周末的间隔天数，我这边执行出来的就是0天啊，你怎么搞出个9来了，我也是醉了
听雨停了 2017-12-05
  • 打赏
  • 举报
 回复
引用 4 楼 weixin_37722207 的回复:
[quote=引用 2 楼 qq_37170555 的回复:] 

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	shenhetime DATETIME,
	shoulitime DATETIME	
)
INSERT INTO #tab
SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL
SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL
SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'
--测试数据结束

--创建函数获取两个日期间的间隔天数(除去周六日)
alter FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	DECLARE @cnt INT
	SET @cnt=0
	SET @begintime=DATEADD(DAY,1,@begintime)
	WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
	BEGIN
		IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
		BEGIN
			SET @cnt=@cnt
		END			
		ELSE 
		BEGIN
			SET @cnt=@cnt+1
		END			
		SET @begintime=DATEADD(DAY,1,@begintime)	
	END
	INSERT INTO @tab
	SELECT @cnt
	RETURN
END

--调用函数计算(前三个小时数相加就是你要的数据了)
SELECT a.*, 
		CASE 
            WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0
            ELSE 18 -DATENAME(hour, shenhetime)
       END                            AS [审核当天小时数当天周末的话就为0)],
        CASE 
            WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0
            ELSE DATENAME(hour, shoulitime) -9
        END                           AS [受理当天总小时数(当天周末的话就为0)],        
        b.cnt*8 AS 间隔小时数,		--一天八小时计算 
		b.cnt AS 间隔天数                              		      
FROM #tab A
OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b
大致就是这样了，你可以试试
我执行了，然后计算出来的时间好像不太对，[/quote] 你这gethours哪里来的啊，你这时间里怎么还有分钟秒啊，这样的话怎么统计啊，用间隔的分钟数/60来算小时数吗？ 你最好把你上面的 select r.id, r.rNumber, r.rTopics, r.rContent, r.rMemo, r.rCreateDate, r.rEndDate, r.rRStatus, r.rTaskPName, a.Actions as shenhe, a.rDateTime as shenhetime, b.actions as shouli, b.rDateTime as shoulitime, (convert(varchar, b.rDateTime-a.rDateTime,108)) balance from tbTaskRD r left join (select *,Row_Number() OVER (partition by rNumber ORDER BY rDateTime desc) seq from tbActions where Actions like ('(审批岗位)%') ) a on r.rNumber=a.rNumber left join (select *,Row_Number() OVER (partition by rNumber ORDER BY rDateTime desc) seq from tbActions where Actions like('(受理岗位)%')) b on r.rNumber=b.rNumber where r.rMethod='' and r.rCreateDate>('2017-01-01 00:00:00.000') and r.rRStatus='完成' and r.rTopics like ('%Support%') 的查询结果发一些数据出来，我再帮你看看
weixin_37722207 2017-12-05
  • 打赏
  • 举报
 回复
引用 2 楼 qq_37170555 的回复:

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	shenhetime DATETIME,
	shoulitime DATETIME	
)
INSERT INTO #tab
SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL
SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL
SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'
--测试数据结束

--创建函数获取两个日期间的间隔天数(除去周六日)
alter FUNCTION getCnt(
@begintime DATETIME,
@endtime DATETIME
)
RETURNS @tab TABLE(cnt int)
AS 
BEGIN
	DECLARE @cnt INT
	SET @cnt=0
	SET @begintime=DATEADD(DAY,1,@begintime)
	WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)
	BEGIN
		IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)
		BEGIN
			SET @cnt=@cnt
		END			
		ELSE 
		BEGIN
			SET @cnt=@cnt+1
		END			
		SET @begintime=DATEADD(DAY,1,@begintime)	
	END
	INSERT INTO @tab
	SELECT @cnt
	RETURN
END

--调用函数计算(前三个小时数相加就是你要的数据了)
SELECT a.*, 
		CASE 
            WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0
            ELSE 18 -DATENAME(hour, shenhetime)
       END                            AS [审核当天小时数当天周末的话就为0)],
        CASE 
            WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0
            ELSE DATENAME(hour, shoulitime) -9
        END                           AS [受理当天总小时数(当天周末的话就为0)],        
        b.cnt*8 AS 间隔小时数,		--一天八小时计算 
		b.cnt AS 间隔天数                              		      
FROM #tab A
OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b
大致就是这样了，你可以试试
我执行了，然后计算出来的时间好像不太对，
weixin_37722207 2017-12-05
  • 打赏
  • 举报
 回复
引用 1 楼 shoppo0505 的回复:
先计算到当天 18:00的工作时间，在家第二天9：00之后的工作时间。
我是SQL小白，能详细一点吗？谢谢！
听雨停了 2017-12-05
  • 打赏
  • 举报
 回复 


--测试数据

IF OBJECT_ID('tempdb..#tab') IS NOT NULL

	DROP TABLE #tab

CREATE TABLE #tab(

	shenhetime DATETIME,

	shoulitime DATETIME	

)

INSERT INTO #tab

SELECT '2017-11-04 17:00:00.000','2017-1-05 09:00:00.000' UNION ALL

SELECT '2017-11-01 12:00:00.000','2017-11-04 12:00:00.000' UNION ALL

SELECT '2017-11-02 09:00:00.000','2017-11-07 15:00:00.000'

--测试数据结束



--创建函数获取两个日期间的间隔天数(除去周六日)

alter FUNCTION getCnt(

@begintime DATETIME,

@endtime DATETIME

)

RETURNS @tab TABLE(cnt int)

AS 

BEGIN

	DECLARE @cnt INT

	SET @cnt=0

	SET @begintime=DATEADD(DAY,1,@begintime)

	WHILE cast(@begintime AS DATE)<cast(@endtime AS DATE)

	BEGIN

		IF(DATEPART(weekday,@begintime)=5 OR DATEPART(weekday,@begintime)=6)

		BEGIN

			SET @cnt=@cnt

		END			

		ELSE 

		BEGIN

			SET @cnt=@cnt+1

		END			

		SET @begintime=DATEADD(DAY,1,@begintime)	

	END

	INSERT INTO @tab

	SELECT @cnt

	RETURN

END



--调用函数计算(前三个小时数相加就是你要的数据了)

SELECT a.*, 

		CASE 

            WHEN DATEPART(weekday, shenhetime) IN (5, 6) THEN 0

            ELSE 18 -DATENAME(hour, shenhetime)

       END                            AS [审核当天小时数当天周末的话就为0)],

        CASE 

            WHEN DATEPART(weekday, shoulitime) IN (5, 6) THEN 0

            ELSE DATENAME(hour, shoulitime) -9

        END                           AS [受理当天总小时数(当天周末的话就为0)],        

        b.cnt*8 AS 间隔小时数,		--一天八小时计算 

		b.cnt AS 间隔天数                              		      

FROM #tab A

OUTER APPLY dbo.getCnt(a.shenhetime,a.shoulitime) b


大致就是这样了，你可以试试
加载更多回复（1）
精通SQL--结构化查询语言详解
第1章 数据库与sql基础 1 1.1 数据库的基本概念 1 1.1.1 数据库的由来 1 1.1.2 数据库系统的概念 3 1.2 数据库系统的结构、组成及工作流程 3 1.2.1 数据库的体系结构 3 1.2.2 数据库系统的组成 4 1.2.3 ...
SQL-剔除周六日，节假日，非工作时间
需求是：求每个请求ID操作时间和验收时间之间的间隔，需要剔除周六日，节假日，非工作时间（我们的工作时间 09:00-17:30 午休时间12:00-13:00 午休时间不属于工作范围，需要剔除） 当看到这个需求的时候，就感觉让...
实战化课程：全面掌握SQL Server高可用技术，从菜鸟到专家
本课程根据讲师十多年在世界500强外企的生产环境中的SQL Serer数据库管理和项目实施经验倾心打造。课程系统性强，知识体系完整，覆盖90%以上的企业环境下SQL Server高可用场景，课程中不仅演示详细的操作步骤，更加突出最常见的故障和问题，让学员少走“弯路”，不只是让学员学会“操作”更能让学员“操作”的规范，满满的干货分享，一些课程资料（架构图、部署规划表格等）不仅可以帮助学员掌握技能，也可以作为学员在企业生产环境中实施SQL Server高可用的配置文档、操作手册等。课程的实验环境介绍：1）全部基于微软域环境和企业版SQL Server AOAG - 95%以上的企业环境都是在域环境中，不介绍非域环境和标准版的SQL Server高可用性组，这的配置在企业中较罕见，没有实践意义，不浪费学员时间。2）相应域环境已提前部署和配置好 - 学员导入虚拟机即可开始实验，无需从零开始搭建域环境，所有实验中SQL Server均已加域，直入主题，节省大量时间。3）最新的Windows Server故障转移集群（WS2016、WS2019）和最新版本的SQL Server（SQL2017、SQL2019） -  WS2016-SQL2017与WS2019-SQL2019是目前大多数企业SQL Server高可用的主要平台，基于微软产品生命周期现在一些企业也在讲早期的AOAG向这两个版本迁移，掌握这两种组合不仅让学员学会，更能学有所用。本课程为后续SQL Server进阶课程铺垫，是通向SQL Server DBA 专家的必经之路，讲师每周答疑两次。所有课程资料包括：课程PPT、架构图、部署规划表格、各类脚本学员均可下载。     
【sql练习】计算时间差的秒数(考虑非工作日)
例如：审批开始时间是2020-02-07 20：09：57，结束时间是2020-02-10 22：30：30，请问审批共耗时多少秒？(扣除所有的法定节假日和周六日) 数据准备 节假日表数据 DROP TABLE IF EXISTS holiday_table; CREATE TABLE ...
mysql两个日期的差去掉非工作时间_SQL 取时间差 去掉周末及非工作时间节假日...
-起讫时间同一天 begin select @MI=DATEDIFF(Mi,@Date_From,@Date_End) end if(@DateF<>@DateE)---起讫时间不是同一天 begin select @work_From=convert(datetime,@DateE+' '+'8:30') select @work_End=convert...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章