如何在MS Access中基于入职日期计算工作年限及年度病假天数

这个需求我太熟了——很多公司的病假政策都是按入职周年重置的，完全不用手动维护周期表，靠SQL的日期函数就能自动搞定。我给你拆解思路和具体实现：

核心就是动态计算员工当前（或指定日期）所属的工作年度周期：

• 工作年度的起始日期是入职日期的「N周年纪念日」（N是入职至今的完整年数，如果当前日期还没到今年的入职周年，就取上一个周年）
• 工作年度的结束日期是起始日期加1年再减1天（比如2016/7/7到2017/7/6）
• 每个周期固定分配16天病假，剩余天数则用16减去该周期内已使用的病假天数（假设你有记录病假使用的表）

下面是几个主流数据库的代码示例，你可以直接套用：

MySQL #

SELECT
    emp_id,
    hire_date,
    -- 计算当前工作年度起始日期
    CASE
        WHEN CURDATE() >= DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
        THEN DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
        ELSE DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) - 1 YEAR)
    END AS pto_year_start,
    -- 工作年度结束日期（起始+1年-1天）
    DATE_SUB(DATE_ADD(
        CASE
            WHEN CURDATE() >= DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
            THEN DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
            ELSE DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) - 1 YEAR)
        END, INTERVAL 1 YEAR), INTERVAL 1 DAY) AS pto_year_end,
    16 AS allocated_sick_days,
    -- 计算剩余天数（关联病假使用表）
    16 - COALESCE(SUM(su.used_days), 0) AS remaining_sick_days
FROM employees e
LEFT JOIN sick_usage su
    ON su.emp_id = e.emp_id
    AND su.usage_date BETWEEN 
        CASE
            WHEN CURDATE() >= DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
            THEN DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
            ELSE DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) - 1 YEAR)
        END
        AND DATE_SUB(DATE_ADD(
            CASE
                WHEN CURDATE() >= DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
                THEN DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) YEAR)
                ELSE DATE_ADD(hire_date, INTERVAL TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) - 1 YEAR)
            END, INTERVAL 1 YEAR), INTERVAL 1 DAY)
GROUP BY emp_id, hire_date;

SQL Server #

SELECT
    emp_id,
    hire_date,
    -- 计算当前工作年度起始日期
    CASE
        WHEN GETDATE() >= DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
        THEN DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
        ELSE DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()) - 1, hire_date)
    END AS pto_year_start,
    -- 工作年度结束日期
    DATEADD(DAY, -1, DATEADD(YEAR, 1, 
        CASE
            WHEN GETDATE() >= DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
            THEN DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
            ELSE DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()) - 1, hire_date)
        END)) AS pto_year_end,
    16 AS allocated_sick_days,
    16 - COALESCE(SUM(su.used_days), 0) AS remaining_sick_days
FROM employees e
LEFT JOIN sick_usage su
    ON su.emp_id = e.emp_id
    AND su.usage_date BETWEEN 
        CASE
            WHEN GETDATE() >= DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
            THEN DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
            ELSE DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()) - 1, hire_date)
        END
        AND DATEADD(DAY, -1, DATEADD(YEAR, 1, 
            CASE
                WHEN GETDATE() >= DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
                THEN DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()), hire_date)
                ELSE DATEADD(YEAR, DATEDIFF(YEAR, hire_date, GETDATE()) - 1, hire_date)
            END))
GROUP BY emp_id, hire_date;

PostgreSQL #

SELECT
    emp_id,
    hire_date,
    -- 计算当前工作年度起始日期
    CASE
        WHEN CURRENT_DATE >= (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
        THEN (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
        ELSE (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) - 1) * INTERVAL '1 year')::DATE
    END AS pto_year_start,
    -- 工作年度结束日期
    (
        CASE
            WHEN CURRENT_DATE >= (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
            THEN (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
            ELSE (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) - 1) * INTERVAL '1 year')::DATE
        END + INTERVAL '1 year' - INTERVAL '1 day'
    )::DATE AS pto_year_end,
    16 AS allocated_sick_days,
    16 - COALESCE(SUM(su.used_days), 0) AS remaining_sick_days
FROM employees e
LEFT JOIN sick_usage su
    ON su.emp_id = e.emp_id
    AND su.usage_date BETWEEN 
        CASE
            WHEN CURRENT_DATE >= (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
            THEN (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
            ELSE (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) - 1) * INTERVAL '1 year')::DATE
        END
        AND (
            CASE
                WHEN CURRENT_DATE >= (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
                THEN (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) * INTERVAL '1 year')::DATE
                ELSE (hire_date + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) - 1) * INTERVAL '1 year')::DATE
            END + INTERVAL '1 year' - INTERVAL '1 day'
        )::DATE
GROUP BY emp_id, hire_date;

如果需要生成员工从入职至今的所有工作年度PTO记录（比如要统计每个周期的病假使用情况），可以用递归CTE自动生成所有周期，完全不用手动维护：

MySQL 递归CTE示例 #

WITH RECURSIVE emp_pto_years AS (
    SELECT
        emp_id,
        hire_date AS pto_year_start,
        DATE_SUB(DATE_ADD(hire_date, INTERVAL 1 YEAR), INTERVAL 1 DAY) AS pto_year_end,
        1 AS year_number
    FROM employees
    UNION ALL
    SELECT
        emp_id,
        DATE_ADD(pto_year_start, INTERVAL 1 YEAR),
        DATE_ADD(pto_year_end, INTERVAL 1 YEAR),
        year_number + 1
    FROM emp_pto_years
    WHERE DATE_ADD(pto_year_start, INTERVAL 1 YEAR) <= CURDATE()
)
SELECT
    epy.emp_id,
    epy.pto_year_start,
    epy.pto_year_end,
    16 AS allocated_sick_days,
    16 - COALESCE(SUM(su.used_days), 0) AS remaining_sick_days
FROM emp_pto_years epy
LEFT JOIN sick_usage su
    ON su.emp_id = epy.emp_id
    AND su.usage_date BETWEEN epy.pto_year_start AND epy.pto_year_end
GROUP BY epy.emp_id, epy.pto_year_start, epy.pto_year_end
ORDER BY epy.emp_id, epy.year_number;

• 所有逻辑都是完全自动的，只要员工的入职日期正确，每次查询都会自动计算对应的工作年度，不需要手动更新任何周期配置。
• 如果要查询指定日期的情况，只需要把代码中的CURDATE()/GETDATE()/CURRENT_DATE替换成你想要的日期即可。

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