按年度拆分计算员工薪资的SQL实现需求及查询优化问询

What We're Solving #

You need to break down each employee's earnings into calendar-year chunks, showing:

• How much they earned in each individual year they worked
• Their cumulative total salary up to the end of that year
  For example:
• Ahmad (hired June 2016, $20k/month) gets two rows: 2016 ($120k) and 2017 ($240k, cumulative $360k)
• Erick (hired Jan 2017, $18k/month) gets one row for 2017 ($216k, cumulative $216k)

Your Existing Setup #

First, let's recap your table and test data (cleaned up for clarity):

CREATE TABLE EmployeeInfo (
    EmpID Int,
    FirstName Varchar(25),
    LastName Varchar(25),
    MonthlySalary Int,
    DOJ Date
);

INSERT INTO EmployeeInfo VALUES 
(1, 'Ahmad', 'Usman', 20000, '2016-06-01'),
(2, 'Erick', 'Ortiz', 18000, '2017-01-01');

The Solution: Recursive CTE for Yearly Breakdown #

We'll use a recursive Common Table Expression (CTE) to generate every calendar year each employee was active, then calculate the relevant salary metrics for each year. Here's the optimized query:

WITH YearlyEmployment AS (
    -- Start with the employee's hire year (anchor member)
    SELECT 
        EmpID,
        FirstName + ' ' + LastName AS EmployeeName,
        MonthlySalary,
        YEAR(DOJ) AS SalaryYear,
        -- Calculate months worked in the first year (from hire date to end of year)
        DATEDIFF(MONTH, DOJ, DATEFROMPARTS(YEAR(DOJ), 12, 31)) + 1 AS MonthsWorked,
        -- Initialize cumulative months with first year's count
        DATEDIFF(MONTH, DOJ, DATEFROMPARTS(YEAR(DOJ), 12, 31)) + 1 AS CumulativeMonths
    FROM EmployeeInfo
    UNION ALL
    -- Add subsequent years until we reach the current year (recursive member)
    SELECT 
        EmpID,
        EmployeeName,
        MonthlySalary,
        SalaryYear + 1,
        -- Full 12 months for complete calendar years
        12,
        CumulativeMonths + 12
    FROM YearlyEmployment
    WHERE SalaryYear + 1 <= YEAR(GETDATE())
)
-- Final output with annual and cumulative salaries
SELECT 
    EmpID,
    SalaryYear AS [Year],
    MonthlySalary,
    MonthlySalary * MonthsWorked AS YearlySalary,
    MonthlySalary * CumulativeMonths AS TotalSalary
FROM YearlyEmployment
ORDER BY EmpID, SalaryYear;

How This Works #

Let's break down the logic so you follow along:

1. Anchor CTE:
   • Grabs each employee's hire year, then calculates how many months they worked in that first year (adding 1 because DATEDIFF counts date differences, not total included months)
   • Sets up the initial cumulative month count with the first year's worked months
2. Recursive CTE:
   • Iteratively adds each subsequent calendar year until we hit the current year (replace YEAR(GETDATE()) with a fixed year if you need to stop at a specific point)
   • For full years, we assume 12 months worked, and add 12 to the cumulative month count
3. Final Select:
   • Computes annual salary (monthly rate × months worked that year) and cumulative total (monthly rate × total months worked up to that year)
   • Orders results by employee ID and year for readability

Sample Output #

When you run this query, you'll get exactly the output you described:

Quick Notes #

• If employees can have salary changes mid-year, you'd need to adjust this logic to account for salary effective dates (but your current setup doesn't include that, so this works perfectly)
• To stop at a specific year instead of the current one, just swap YEAR(GETDATE()) with your target year (e.g., 2024)

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