SQL - 获取多个范围之间的时间总和-解网

问：

上图代表了我一直在尝试在MSSQL中完成的事情。

这是一个具有多个时间范围的时间表，A 到 B 是加班，B 到 C 是早上的正常工作，C 是 D 的午餐时间，D 到 E 是下午的正常工作，E 到 F 也是加班。

为简单起见，我手动将 A、B、C、D、E 和 F 声明为固定值。实际上，这些将从另一个表中馈送，但这与此问题无关。

垂直箭头表示上班/下班时间。

所以，假设 IN 是早上 8 点，OUT 是晚上 7 点。

如何检索以下内容？

工作时间：8小时
午餐：1小时
加班时间：2小时

例如，无需使用一堆 IF 并且知道 IN 可能位于 B 和 C 的中间。

早上 11 点上班，晚上 7 点下班的人现在工作 = 6 小时，午餐 = 1 小时，加班 = 1 小时。

谢谢。

举个例子，我目前的意大利面条代码，肯定有一种更优雅（和更干净）的方法来实现这一目标。

declare @clockIn time = '08:00'
declare @clockOut time = '19:00'

declare @a time= '00:00'
...all possible limits defined here...
declare @f time= '23:59'

tests using C to D only:

-- in block
IF(@clockIn >= @c
   AND @clockOut <= @d)
    BEGIN
        SET @at = @at + ABS(DATEDIFF(hour, @clockIn, @clockOut));
    END;
-- outside block
IF(@clockIn < @c
   AND @clockOut > @d)
    BEGIN
        SET @at = @at + ABS(DATEDIFF(hour, @c, @d));
    END;
-- cIn in block, cOut outside
IF(@clockIn >= @c
   AND @clockOut > @d)
    BEGIN
        SET @at = @at + ABS(DATEDIFF(hour, @clockIn, @d));
    END;
-- cIn outside, cIn inside
IF(@clockIn < @c
   AND @clockOut <= @d)
    BEGIN
        SET @at = @at + ABS(DATEDIFF(hour, @c, @clockOut));
    END
select @ax as overtime,@at as work,@ai as lunch

SQL Server 数学逻辑逻辑

好吧，你必须以一种或另一种方式构建业务逻辑。就我个人而言，我会尝试将其构建为一个尽可能简洁的案例表达式（在包含工作时间的表和包含常规时间的表之间有一个交叉连接），同时为任何遇到它的人留下有关业务逻辑的评论。

答：

1赞 ZLK 2/20/2019 #1

我想不出一种方法，你可以不以某种方式构建if-then逻辑来做到这一点，所以这是一个你如何做的问题。我认为最简单（也许是最易读）的方法是在每次需要的时候都使用一个大小写表达式，也许会留下一些关于它背后的逻辑的评论，这样任何遇到它的人都可以更容易地理解它是如何工作的。

例如，像这样的东西应该足够简单，而不会太混乱（IMO）：

DECLARE @Clock TABLE (Clockin TIME NOT NULL, Clockout TIME NOT NULL);
INSERT @Clock VALUES ('12:30', '19:00');

DECLARE @Times TABLE (WorkStart TIME NOT NULL, LunchStart TIME NOT NULL, LunchEnd TIME NOT NULL, WorkEnd TIME NOT NULL);
INSERT @Times VALUES ('09:00', '12:00', '13:00', '18:00');

SELECT 
    OverTime = 
        (CASE -- Overtime hours in the morning.
            WHEN C.Clockin < T.WorkStart -- Clocked in before official work start.
            THEN DATEDIFF(MINUTE, C.Clockin, CASE WHEN C.Clockout > T.WorkStart THEN T.WorkStart ELSE C.Clockout END) 
            ELSE 0 
        END + 
        CASE -- Overtime hours in the evening.
            WHEN C.Clockout > T.WorkEnd -- Clocked out after official work end.
            THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin > T.WorkEnd THEN C.Clockin ELSE T.WorkEnd END, C.Clockout) 
            ELSE 0 
        END) / 60.0,
    Work =
        (CASE -- Work hours for the morning.
            WHEN C.Clockout > T.WorkStart AND C.Clockin < T.LunchStart -- Clocked out after official work start and clocked in before official lunch start.
            THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin < T.WorkStart THEN T.WorkStart ELSE C.Clockin END, CASE WHEN C.Clockout < T.LunchStart THEN C.Clockout ELSE T.LunchStart END) 
            ELSE 0
        END + 
        CASE -- Work hours for the afternoon.
            WHEN C.Clockin < T.WorkEnd AND C.Clockout > T.LunchEnd -- Clocked out after official lunch end and clocked in before official work end.
            THEN DATEDIFF(MINUTE,CASE WHEN C.Clockin < T.LunchEnd THEN T.LunchEnd ELSE C.Clockin END, CASE WHEN C.Clockout < T.WorkEnd THEN C.Clockout ELSE T.WorkEnd END) 
            ELSE 0
        END) / 60.0,
    Lunch =
        CASE -- Lunch hours.
            WHEN C.Clockin < T.LunchEnd AND C.Clockout > T.LunchStart -- Clocked in before official lunch end and clocked out after official lunch start.
            THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin > T.LunchStart THEN C.Clockin ELSE T.LunchStart END, CASE WHEN C.Clockout < T.LunchEnd THEN C.Clockout ELSE T.LunchEnd END) 
            ELSE 0
        END / 60.0
FROM @Clock AS C
CROSS JOIN @Times AS T;

上一个：为简单的布尔 WHERE 子句而苦苦挣扎

下一个：条件格式：根据与另一个单元格的比较为单元格着色

SQL - 获取多个范围之间的时间总和

SQL - Getting the sum of times between multiple ranges

评论