两行之间的时间差，不包括周末和时间戳

Question

1

日期		ID	值
2022-10-07 17:30:00.000	1
2022-10-10 10:00:00.000	2	2
2022-10-12 08:31:42.000		3	1

首先，让我们计算需要处理的值，即当前日期(我们已经将其作为字段(和上一个日期(使用LAG窗口函数，就像您在尝试中所做的那样(。

WITH cte AS (
SELECT [date] AS curDate, 
LAG([date]) OVER(ORDER BY date) AS prevDate
FROM tab
)

---

案例1：prevDate的日期等于curDate的日期

在这种情况下，我们只需要应用两个日期之间的分钟差，如下所示：

CASE WHEN CONVERT(DATE, curDate) = CONVERT(DATE, prevDate)
THEN DATEDIFF(MINUTE, prevDate, curDate)

---

案例2：prevDate的日期不等于curDate的日期

这里我们需要计算三件事：

• endtime(18:00:00(和prevDate时间之间的时间差

DATEDIFF(MINUTE, CAST(prevDate AS TIME), '18:00:00')

• starttime(9:00:00(和curDate时间之间的时间差

DATEDIFF(MINUTE, '9:00:00', CAST(curDate AS TIME))

• curDate和prevDate之间的天数差异。对于最后一个，有一个关于周末的警告。我们可以通过检查curDate的星期几是否小于prevDate的星期天来解决这个问题(如果prevDate的工作日期是星期五，curDate的工作时间是星期一，那么星期一的工作日=2，星期五的工作日=6，而2-6<0(。如果结果小于0，那么我们需要减去3天的工作(乘以每天9天的工作活动-18:00:00-9:00:00(

DATEDIFF(MINUTE, '9:00:00', CAST(curDate AS TIME))   +
DATEDIFF(MINUTE, CAST(prevDate AS TIME), '18:00:00') +
9*(DATEDIFF(DAY, prevDate, curDate) -
CASE WHEN DATEPART(WEEKDAY, curDate) - DATEPART(WEEKDAY, prevDate) > 1  
THEN 0 
ELSE 3 END
)

---

因此最终查询将是以下查询：

WITH cte AS (
SELECT [date] AS curDate, 
LAG([date]) OVER(ORDER BY date) AS prevDate
FROM tab
)
SELECT curDate,
CASE WHEN CONVERT(DATE, curDate) = CONVERT(DATE, prevDate)
THEN DATEDIFF(MINUTE, prevDate, curDate)
ELSE DATEDIFF(MINUTE, '9:00:00', CAST(curDate AS TIME))   +
DATEDIFF(MINUTE, CAST(prevDate AS TIME), '18:00:00') +
9*(DATEDIFF(DAY, prevDate, curDate) -
CASE WHEN DATEPART(WEEKDAY, curDate) - DATEPART(WEEKDAY, prevDate) > 1
THEN 0 
ELSE 3 END)
END
FROM cte

请在此处查看演示。