请求协助开发Oracle SQL员工入职周年查询语句

Got it, let's build that Oracle SQL query for employee work anniversaries. Here's a solution that meets all your requirements—including historical anniversary data and date range filtering:

Core Query #

This query generates annual anniversary records for each employee, filters them to your specified date range, and returns the exact columns you need:

WITH employee_anniversaries AS (
    SELECT
        NAME,
        TRUNC(DATE_OF_EMPLOYMENT) AS hire_date,
        ADD_MONTHS(TRUNC(DATE_OF_EMPLOYMENT), 12 * level) AS anniversary_date,
        level AS years_with_business
    FROM
        YOUR_EMPLOYEE_TABLE -- Replace with your actual table name
    CONNECT BY
        ADD_MONTHS(TRUNC(DATE_OF_EMPLOYMENT), 12 * level) <= :END_DATE -- End of your date range
        AND PRIOR NAME = NAME
        AND PRIOR SYS_GUID() IS NOT NULL -- Prevents cyclic loops for multiple employees
)
SELECT
    NAME,
    TO_CHAR(anniversary_date, 'YYYY-MM-DD') AS "ANNIVERSARY DATE",
    years_with_business AS "YEARS WITH BUSINESS"
FROM
    employee_anniversaries
WHERE
    anniversary_date BETWEEN :START_DATE AND :END_DATE -- Filter to your target date range
ORDER BY
    NAME,
    anniversary_date;

How It Works #

Let's break down the key parts:

• TRUNC(DATE_OF_EMPLOYMENT): Removes any time component from the hire date to ensure clean date comparisons.
• CONNECT BY Clause: Generates a sequence of annual anniversaries for each employee. The level value represents how many years the employee has been with the business.
• ADD_MONTHS(..., 12*level): Calculates the exact anniversary date each year, automatically handling leap years (e.g., a Feb 29 hire becomes Feb 28 in non-leap years).
• :START_DATE and :END_DATE: Replace these with your desired date range (e.g., '2023-01-01' and '2023-12-31') or use bind variables in your application.

Optional: Include the "0th Anniversary" (Hire Date) #

If you want to include the employee's actual hire date as a "0 years with business" entry, adjust the query to start the sequence at level 0:

WITH employee_anniversaries AS (
    SELECT
        NAME,
        TRUNC(DATE_OF_EMPLOYMENT) AS hire_date,
        ADD_MONTHS(TRUNC(DATE_OF_EMPLOYMENT), 12 * level) AS anniversary_date,
        level AS years_with_business
    FROM
        YOUR_EMPLOYEE_TABLE
    CONNECT BY
        ADD_MONTHS(TRUNC(DATE_OF_EMPLOYMENT), 12 * level) <= :END_DATE
        AND PRIOR NAME = NAME
        AND PRIOR SYS_GUID() IS NOT NULL
    START WITH level = 0 -- Starts counting from the hire date itself
)
SELECT
    NAME,
    TO_CHAR(anniversary_date, 'YYYY-MM-DD') AS "ANNIVERSARY DATE",
    years_with_business AS "YEARS WITH BUSINESS"
FROM
    employee_anniversaries
WHERE
    anniversary_date BETWEEN :START_DATE AND :END_DATE
ORDER BY
    NAME,
    anniversary_date;

Quick Notes #

• Replace YOUR_EMPLOYEE_TABLE with your actual employee table name.
• If your hire date field has a different name (like "date of employment" with spaces), wrap it in double quotes: "date of employment".

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