跨日班次有效工时（扣除休息）秒数计算优化咨询

Great question! Dealing with shift schedules that wrap around midnight is always a headache, and while your current view works, it's definitely ripe for simplification to boost readability and maintainability. Let's break down a cleaner approach:

Core Improvements We'll Make #

• Split complex calculations into modular chunks using Common Table Expressions (CTEs)
• Simplify cross-day duration logic with DATEADD instead of manually splitting the day at 23:59
• Consolidate overlapping case logic for rest period deductions and shift status

Optimized View Code #

CREATE VIEW v_LaborTime AS
WITH ShiftBasics AS (
    SELECT
        cdLaborTime,
        nmLaborTime,
        beginLaborTime,
        endLaborTime,
        beginInterval,
        endInterval,
        -- Calculate total shift duration (handles cross-day shifts automatically)
        DATEDIFF(SECOND, beginLaborTime, 
                 DATEADD(DAY, CASE WHEN beginLaborTime > endLaborTime THEN 1 ELSE 0 END, endLaborTime)) AS TotalShiftSeconds,
        -- Fixed rest period duration
        DATEDIFF(SECOND, beginInterval, endInterval) AS RestSeconds,
        -- Cache current time's TIME part to avoid repeated casts
        CAST(GETDATE() AS TIME) AS CurrentTime
    FROM laborTime
),
CurrentShiftMetrics AS (
    SELECT
        *,
        -- Calculate raw seconds from shift start to current time (cross-day aware)
        CASE
            WHEN beginLaborTime <= CurrentTime THEN DATEDIFF(SECOND, beginLaborTime, CurrentTime)
            ELSE DATEDIFF(SECOND, beginLaborTime, '23:59:59') + DATEDIFF(SECOND, '00:00:00', CurrentTime) + 1
        END AS RawCurrentSeconds,
        -- Calculate how much rest time to deduct based on current time
        CASE
            WHEN CurrentTime <= beginInterval THEN 0
            WHEN CurrentTime BETWEEN beginInterval AND endInterval THEN DATEDIFF(SECOND, beginInterval, CurrentTime)
            ELSE RestSeconds
        END AS DeductRestSeconds,
        -- Simplified shift status check
        CASE
            WHEN CurrentTime BETWEEN beginLaborTime AND endLaborTime THEN 'OPEN'
            WHEN beginLaborTime > endLaborTime AND (CurrentTime >= beginLaborTime OR CurrentTime < endLaborTime) THEN 'OPEN'
            ELSE 'CLOSE'
        END AS cdState
    FROM ShiftBasics
)
SELECT
    TotalShiftSeconds - RestSeconds AS TotalSeconds,
    RawCurrentSeconds - DeductRestSeconds AS CurrentSeconds,
    cdState,
    cdLaborTime,
    nmLaborTime,
    beginLaborTime,
    endLaborTime,
    beginInterval,
    endInterval
FROM CurrentShiftMetrics;

Why This Works Better #

1. Modular CTEs: Each CTE handles one specific part of the calculation—ShiftBasics sets up foundational values, CurrentShiftMetrics computes time-specific metrics. This makes it easy to tweak individual parts (like rest deduction rules) without touching the entire query.
2. Simplified Cross-Day Calculation: Instead of manually adding seconds from shift start to 23:59 and 00:00 to shift end, we use DATEADD to add a day to the end time if the shift wraps around midnight. This is more intuitive and less error-prone.
3. Consolidated Rest Deduction: The rest period deduction logic is condensed into three clear cases, eliminating the tangled nested conditions from your original view.
4. Cached Current Time: We cast GETDATE() to TIME once in the first CTE, avoiding repeated casts across multiple case statements (small performance win, plus cleaner code).
5. Clearer Status Check: The shift status logic explicitly splits non-cross-day and cross-day scenarios, making it easy to verify if the logic matches your business rules.

Quick Note on Edge Cases #

• We use 23:59:59 instead of 23:59 to ensure accurate second calculations (since DATEDIFF(SECOND, '23:59', '00:00') returns a negative value). The +1 accounts for the final second of the day.
• If your rest periods ever start/end across midnight (unlikely based on your sample data), you can extend the rest deduction logic using the same cross-day approach we used for shifts.

内容的提问来源于stack exchange，提问作者Rogério Carvalho Batista