MySQL：计算员工2018年度基于odate与edate的总日期差

Got it, let's work through this problem. You need to sum up the valid date intervals a specific emp_id has within the 2018 calendar year, adjusting for ranges that spill outside 2018, and anchor the calculation to January 1, 2018. Here's a robust solution that handles all edge cases:

Step-by-Step SQL Query #

SELECT 
    SUM(DATEDIFF(
        -- Use the earlier of the employee's end date or 2018's last day
        LEAST(edate, '2018-12-31'),
        -- Use the later of the employee's start date or 2018's first day
        GREATEST(odate, '2018-01-01')
    ) + 1) AS total_valid_days_2018
FROM 
    your_table_name  -- Replace with your actual table name
WHERE 
    emp_id = 'target_emp_id'  -- Replace with your specific employee ID
    -- Only include records that overlap with 2018 at all
    AND edate >= '2018-01-01' 
    AND odate <= '2018-12-31';

Key Breakdown #

• GREATEST(odate, '2018-01-01'): This fixes cases where an employee's start date is before 2018—we'll start counting from the first day of the year instead of including pre-2018 days.
• LEAST(edate, '2018-12-31'): For employees whose end date is after 2018, we cap the interval at the last day of 2018 to avoid counting post-2018 days.
• DATEDIFF(end, start) + 1: The DATEDIFF function returns the gap between two dates, but doesn't include the start date. Adding 1 ensures we count both the start and end days of the valid interval.
• The WHERE clause: Filters out any records that don't overlap with 2018 (like an employee who started after 2018 or ended before it) so they don't skew the sum.

Example Scenarios #

Let’s verify with sample data to make sure it works:

• If an employee has odate = '2017-11-01' and edate = '2018-02-15', the valid range becomes 2018-01-01 to 2018-02-15, totaling 46 days (DATEDIFF('2018-02-15','2018-01-01') + 1 = 45 + 1 = 46).
• If an employee has odate = '2018-05-01' and edate = '2019-03-01', the valid range is 2018-05-01 to 2018-12-31, totaling 245 days.
• If a record’s odate = '2019-01-01' or edate = '2017-12-31', it gets filtered out entirely by the WHERE clause and doesn’t contribute to the sum.

内容的提问来源于stack exchange，提问作者Arpan