gpt4 book ai didi

SQL DateDiff 高级用法?

转载 作者:行者123 更新时间:2023-12-05 00:17:57 29 4
gpt4 key购买 nike

我需要计算两个日期之间的 DateDiff(小时),但仅限于工作时间(8:30 - 16:00,没有周末)。然后,按照下面的示例,此结果将放入 Reaction_Time 列中。

ID 日期 react _超时时间
1 29.04.2003 15:00:00
1 30.04.2003 11:00:00 3:30
2 30.04.2003 14:00:00
2 01.05.2003 14:00:00 7:30 是

*注意:我没有检查示例中的日期是否为假期。

我使用的是 SQL Server 2005

这将与一个更大的查询结合起来,但现在我需要的只是这个来开始,我将尝试弄清楚如何自己把它们放在一起。谢谢您的帮助!

编辑:嘿,谢谢大家的回复。但由于 SQL 端解决方案的明显复杂性,我们决定在 Excel 中执行此操作,因为无论如何都将在 Excel 中移动报表。很抱歉给您带来麻烦,但我真的认为这会比这更简单。事实上,我们只是没有时间。

最佳答案

我建议构建一个用户定义的函数,根据您的规则计算营业时间的日期差异。

SELECT
Id,
MIN(Date) DateStarted,
MAX(Date) DateCompleted,
dbo.udfDateDiffBusinessHours(MIN(Date), MAX(Date)) ReactionTime
FROM
Incident
GROUP BY
Id

我不知道你在哪里 Overdue值(value)来自,所以我在我的例子中没有提到它。

在函数中,您可以编写比在查询中更具表现力的 SQL,并且您不会用业务规则阻塞查询,从而使其难以维护。

一个函数也可以很容易地重用。将其扩展为包括对假期的支持(我正在考虑这里的 Holidays 表)不会太难。无需更改难以读取的嵌套 SELECT/CASE WHEN 构造,即可进行进一步的改进,这将是替代方案。

如果我今天有时间,我会考虑编写一个示例函数。

编辑:这里有一些花里胡哨的东西,在周末透明地计算:
ALTER FUNCTION dbo.udfDateDiffBusinessHours (
@date1 DATETIME,
@date2 DATETIME
) RETURNS DATETIME AS
BEGIN
DECLARE @sat INT
DECLARE @sun INT
DECLARE @workday_s INT
DECLARE @workday_e INT
DECLARE @basedate1 DATETIME
DECLARE @basedate2 DATETIME
DECLARE @calcdate1 DATETIME
DECLARE @calcdate2 DATETIME
DECLARE @cworkdays INT
DECLARE @cweekends INT
DECLARE @returnval INT

SET @workday_s = 510 -- work day start: 8.5 hours
SET @workday_e = 960 -- work day end: 16.0 hours

-- calculate Saturday and Sunday dependent on SET DATEFIRST option
SET @sat = CASE @@DATEFIRST WHEN 7 THEN 7 ELSE 7 - @@DATEFIRST END
SET @sun = CASE @@DATEFIRST WHEN 7 THEN 1 ELSE @sat + 1 END

SET @calcdate1 = @date1
SET @calcdate2 = @date2

-- @date1: assume next day if start was after end of workday
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) > @workday_e
THEN @basedate1 + 1
ELSE @calcdate1
END

-- @date1: if Saturday or Sunday, make it next Monday
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE DATEPART(dw, @basedate1)
WHEN @sat THEN @basedate1 + 2
WHEN @sun THEN @basedate1 + 1
ELSE @calcdate1
END

-- @date1: assume @workday_s as the minimum start time
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) < @workday_s
THEN DATEADD(mi, @workday_s, @basedate1)
ELSE @calcdate1
END

-- @date2: assume previous day if end was before start of workday
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) < @workday_s
THEN @basedate2 - 1
ELSE @calcdate2
END

-- @date2: if Saturday or Sunday, make it previous Friday
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE DATEPART(dw, @calcdate2)
WHEN @sat THEN @basedate2 - 0.00001
WHEN @sun THEN @basedate2 - 1.00001
ELSE @date2
END

-- @date2: assume @workday_e as the maximum end time
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) > @workday_e
THEN DATEADD(mi, @workday_e, @basedate2)
ELSE @calcdate2
END

-- count full work days (subtract Saturdays and Sundays)
SET @cworkdays = DATEDIFF(dd, @basedate1, @basedate2)
SET @cweekends = @cworkdays / 7
SET @cworkdays = @cworkdays - @cweekends * 2

-- calculate effective duration in minutes
SET @returnval = @cworkdays * (@workday_e - @workday_s)
+ @workday_e - DATEDIFF(mi, @basedate1, @calcdate1)
+ DATEDIFF(mi, @basedate2, @calcdate2) - @workday_e

-- return duration as an offset in minutes from date 0
RETURN DATEADD(mi, @returnval, 0)
END

该函数返回 DATETIME值表示从日期 0(即 "1900-01-01 00:00:00" )的偏移量。例如,8:00 小时的时间跨度将是 "1900-01-01 08:00:00" 25 小时将是 "1900-01-02 01:00:00" .函数结果是两个日期之间营业时间的时差。没有对加类的特殊处理/支持。
SELECT dbo.udfDateDiffBusinessHours('2003-04-29 15:00:00', '2003-04-30 11:00:00')
--> 1900-01-01 03:30:00.000

SELECT dbo.udfDateDiffBusinessHours('2003-04-30 14:00:00', '2003-05-01 14:00:00')
--> 1900-01-01 07:30:00.000

@date1 出现时,该函数假定下一个可用工作日(08:30 h)开始。下类时间,以及上一个可用工作日(16:00 h)的结束时间,当 @date2下类了。

“下一个/上一个可用”是指:
  • 如果 @date1'2009-02-06 07:00:00' (周五),会变成'2009-02-06 08:30:00' (周五)
  • 如果 @date1'2009-02-06 19:00:00' (周五),会变成'2009-02-09 08:30:00' (周一)
  • 如果 @date2'2009-02-09 07:00:00' (星期一),它会变成'2009-02-06 16:00:00' (周五)
  • 如果 @date2'2009-02-09 19:00:00' (星期一),它会变成'2009-02-09 16:00:00' (周一)
  • 关于SQL DateDiff 高级用法?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/593898/

    29 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com