指定年份员工月度薪资查询：现有SQL语句优化需求

Got it, let's tweak your existing query to only pull monthly salary data for a specified year (like 2017) and also fix a potential issue where multiple historical records might create duplicate rows for the same month. Here's the optimized version:

DECLARE @TargetYear INT = 2017; -- Set your desired year here easily

; WITH DS AS (
    -- Generate first day of each month in the target year only
    SELECT DATEFROMPARTS(@TargetYear, 1, 1) AS [Date]
    UNION ALL
    SELECT DATEADD(Month, 1, [Date])
    FROM DS
    WHERE [Date] < DATEFROMPARTS(@TargetYear, 12, 1) -- Stop at Nov to generate Dec as the last month
)
SELECT 
    EMP.EmpID,
    DATENAME(month, DS.[Date]) AS [Month],
    YEAR(DS.[Date]) AS [Year],
    -- Use latest applicable salary: historical if available, else base salary
    ISNULL(HIST.LatestSalary, EMP.Salary) AS Salary,
    ISNULL(HIST.LatestStatus, EMP.EmployementStatus) AS [Status]
FROM EmployeeInfo AS EMP
INNER JOIN DS 
    ON EMP.HiringDate <= EOMONTH(DS.[Date]) -- Ensure we only include months after the employee was hired
OUTER APPLY (
    -- Grab the most recent historical record that applies to the current month
    SELECT TOP 1 
        HIS.NewSalary AS LatestSalary,
        HIS.UpdatedEmployementStatus AS LatestStatus
    FROM EmployeeInfoHistory AS HIS
    WHERE HIS.EmpID = EMP.EmpID
      AND HIS.PromotionDate <= EOMONTH(DS.[Date])
    ORDER BY HIS.PromotionDate DESC
) AS HIST
WHERE 
    YEAR(DS.[Date]) = @TargetYear
    AND EMP.HiringDate <= EOMONTH(DS.[Date]) -- Double-check to exclude pre-hire months
OPTION (MAXRECURSION 100) -- No need for 10000 since we only generate 12 months max

Key Improvements Explained: #

• Flexible Target Year: Added a @TargetYear variable so you can switch between years without rewriting the entire query.
• Efficient Date Range: The CTE now only generates months for your target year instead of a 25-year span, cutting down on unnecessary processing.
• Eliminated Duplicate Rows: Used OUTER APPLY with TOP 1 and ORDER BY PromotionDate DESC to ensure we only get the latest salary change that applies to each month—no more duplicate entries from multiple historical records.
• Accurate Hire Date Checks: Used EOMONTH to compare against the end of each month, making sure we don't include months before an employee joined.
• Reduced Recursion: Lowered MAXRECURSION to 100 since we only need 12 months, which is more efficient than the original 10000.

Example 2017 Output: #

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