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%')
...全文
1561 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）
Oracle 计算非工作时间.sql
Oracle 计算非工作时间（去除周末，自定义节假日，等待时间）
精通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 ...
MSSQL 计算两个日期相差的工作天数的语句
在SQL Server中，有时我们需要计算两个日期之间相差的工作日数量，即排除周末和法定假日的天数。在提供的示例中，给出了两种不同的T-SQL函数实现方式来完成这个任务，这两种方法都针对MSSQL数据库系统。我们将分别...
工作中常用的mysql语句分享 不用php也可以实现的效果
2. **计算时间差** 要获取两个日期时间之间的差值，你可以利用`UNIX_TIMESTAMP()`函数： ```sql SELECT ((UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)) / 60) AS minute_diff, ((UNIX_TIMESTAMP(dt2) - UNIX_...
oracle 获得节假日,Oracle 计算两个日期间时间排除非工作日及非工作时间
Oracle 计算两个日期间时间排除非工作日及非工作时间精确至分钟客户要求计算两个时间点之间工作了多少时间，时间上要排除非工作日、节假日、非工作时间。非工作时间设定为除9点到18点之外的。网上计算时间点时是自己...
疑难问题

22,298

社区成员

121,731

社区内容

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

加载中

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

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

+ 用AI写文章