T-SQL |计算日期之间的小时数，但忽略周末的小时数，仅计算8-18小时 [英] T-SQL | Calculate the number of hours between dates but ignore the hours from Weekend and only count 8-18 hours

问题描述

我正在尝试计算除周末以外的两个日期之间的时差，仅计算从晚上8点至凌晨6点的时间。我想计算出这种差异，以天，小时和分钟为单位。

I'm trying to calculate the difference between two dates excluding the weekends and only count the time from 8pm - 6am. I want to calculate that difference in Days, Hours and Minutes.

为此，我有这个：

DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '2017-06-23 10:43:41.000'
SET @End_Date = '2017-06-27 11:58:52.000'

SELECT (DATEDIFF(dd, @Start_Date, @End_Date) + 1)
                -(DATEDIFF(wk, @Start_Date, @End_Date) * 2)
                -(CASE WHEN DATENAME(dw, @Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
                -(CASE WHEN DATENAME(dw, @End_Date) = 'Saturday' THEN 1 ELSE 0 END) AS [Time to First Atualization- Days],
            datediff(hour, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 48) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Hours],
                datediff(minute, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 2880) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Minutes]

天查询返回正确的值，但要计算小时和分钟数是错误的...

The number of days the query return the correct value but to calculate the number of hours and minutes it's wrong...

我该如何解决？

谢谢！

推荐答案

我从头开始研究了一些东西，似乎可以满足您的所有需求，

I worked out something from scratch, and it seems to cover all your needs, though you can update us back if something's missing.

考虑到这是一个全新的起点，并且从不同的角度出发，您可能会发现其中的某些技术或想法。另外，对我来说似乎更简单，但这也许是因为我正在审阅自己的作品...

Considering it's a fresh start and coming from a different angle, you might discover certain techniques or ideas out of it. Also, it does seem simpler to me but maybe that's because I'm reviewing my own work...

最后一点，我将依靠一种技巧之前阅读过，以逐行方式应用 MIN 和 MAX ，抽象示例：

One last note, I'll be relying on a trick I read before, that applies MIN and MAX in a row-wise fashion, abstract example:

SELECT MAX([value]) AS [MAX], MIN([value]) AS [MIN]
FROM (
VALUES (CURRENT_TIMESTAMP), (@Start_Date), (@End_Date), (NULL), (0)
) AS [data]([value])

首先，想一想开始和结束时间的长短。结束日期：

First off, thought of figuring the amount of time outside start & end days:

SELECT MinutesExcludingStartAndEndDays = MAX([value])
FROM (VALUES (0), ((DATEDIFF(DAY, @Start_Date, @End_Date) - 1) * 840)) AS [data]([value])

第二步，计算开始时间相对于晚上8点的时间（如果两天都匹配，则为或结束时间）：

Second, figuring the time during starting day, against 8pm (or end time if both days match):

SELECT MinutesOnStartDay = DATEDIFF(MINUTE, @Start_Date, MIN([value]))
FROM (VALUES (@End_Date), (DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 20, 0, 0, 0))) AS [data]([value])

第三与第二非常相似，但是请注意，如果开始日期和结束日期相同，则我们不应同时计算第二和第三天。我决定在第三行中使用 CASE 语句来处理该问题：

Third is very similar to second, however note that if start and end days were the same, we should not count both second and third. I decided to handle that with a CASE statement within third:

SELECT MinutesOnEndDayIfNotStartDay = CASE DATEDIFF(DAY, @Start_Date, @End_Date) WHEN 0 THEN 0 ELSE DATEDIFF(MINUTE, MAX([value]), @End_Date) END
FROM (VALUES (@Start_Date), (DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 6, 0, 0, 0))) AS [data]([value])

第四，如果开始日期或结束日期是在周末，则应将其从那里移开：

Fourth, if either start or end dates land on a weekend, it should be pushed away from there:

DECLARE @Mod int

SET @Mod = CONVERT(int, @Start_Date) % 7
IF @Mod IN (5, 6)
    SET @Start_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 6, 0, 0, 0))

SET @Mod = CONVERT(int, @End_Date) % 7
IF @Mod IN (5, 6)
    SET @End_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN -1 WHEN 6 THEN -2 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 20, 0, 0, 0))

最后，在目标时期内完全包含周末的问题，为此请查看这个问题，从选票中我只能猜到他们已经解决了。

Lastly, the issue of having weekend days fully encompassed within your target period, for that have a look at this question, from the votes there I can only guess they worked it out already.

这篇关于T-SQL |计算日期之间的小时数，但忽略周末的小时数，仅计算8-18小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！