SSRS报表开发：SQL自学者求教HourlyPayRate列均值计算与新表生成

没问题，我来帮你一步步搞定这两个需求——计算HourlyPayRate的平均值，还要把这个结果存入新表，刚好结合你现有的SQL代码来调整：

首先，咱们可以用**CTE（公共表表达式）**或者子查询，先把你原来查询里生成的HourlyPayRate列算出来，再在此基础上求平均值。这样逻辑更清晰，也方便后续扩展。

举个用CTE的例子，完美适配你的现有代码：

DECLARE @PROCLEVEL varchar(6), @HRYEAR varchar(5), @HRPERIOD varchar(15)
SET @PROCLEVEL = 'XXX'
SET @HRYEAR = '2019'
SET @HRPERIOD = '03'

-- 先用CTE生成包含HourlyPayRate的数据集
WITH EmployeePayData AS (
    SELECT 
        CASE 
            WHEN HRPERIOD = 3 THEN 'March' 
            WHEN HRPERIOD = 6 THEN 'June' 
            WHEN HRPERIOD = 9 THEN 'September' 
            WHEN HRPERIOD = 12 THEN 'DECEMBER' 
        END HRPERIOD, 
        EMPLOYEE, 
        HRYEAR, 
        PAY_RATE, 
        FTE_TOTAL, 
        JOB_CLASS_NAME, 
        JOB_CLASS, 
        SALARY_CLASS, 
        CASE 
            WHEN SALARY_CLASS = 'S' OR PAY_RATE > 1000 THEN (PAY_RATE / 2080) 
            ELSE PAY_RATE 
        END AS HourlyPayRate 
    FROM [LAWSONDWHR].[dbo].[RPT_EMPLOYEECENSUS_ASOF] 
    WHERE PROCESS_LEVEL = @PROCLEVEL 
        AND HRYEAR = @HRYEAR 
        AND HRPERIOD = @HRPERIOD 
        AND JOB_CLASS = 'RN' 
        AND FTE_TOTAL != 0 
        AND MASTER_EMP_STATUS NOT IN ('ZZ', 'T1')
)
-- 从CTE里计算平均值
SELECT AVG(HourlyPayRate) AS AvgHourlyPayRate
FROM EmployeePayData;

如果想同时保留原始员工数据和平均值，也可以用窗口函数AVG() OVER()，这样每一行都会显示整体的平均值，比如：

-- 就在你原来的SELECT里加一列
SELECT 
    -- 你的其他列...
    CASE 
        WHEN SALARY_CLASS = 'S' OR PAY_RATE > 1000 THEN (PAY_RATE / 2080) 
        ELSE PAY_RATE 
    END AS HourlyPayRate,
    AVG(CASE 
            WHEN SALARY_CLASS = 'S' OR PAY_RATE > 1000 THEN (PAY_RATE / 2080) 
            ELSE PAY_RATE 
        END) OVER() AS AvgHourlyPayRate
-- 你的FROM和WHERE部分不变...
ORDER BY PAY_RATE;

这里分两种情况，根据你的新表是否已经存在来选择：

情况1：新表还不存在（自动创建表） #

用SELECT INTO语句，它会自动创建新表，并且把查询结果插入进去。比如：

DECLARE @PROCLEVEL varchar(6), @HRYEAR varchar(5), @HRPERIOD varchar(15)
SET @PROCLEVEL = 'XXX'
SET @HRYEAR = '2019'
SET @HRPERIOD = '03'

WITH EmployeePayData AS (
    -- 这里和上面的CTE内容完全一样
    SELECT 
        CASE 
            WHEN HRPERIOD = 3 THEN 'March' 
            WHEN HRPERIOD = 6 THEN 'June' 
            WHEN HRPERIOD = 9 THEN 'September' 
            WHEN HRPERIOD = 12 THEN 'DECEMBER' 
        END HRPERIOD, 
        EMPLOYEE, 
        HRYEAR, 
        PAY_RATE, 
        FTE_TOTAL, 
        JOB_CLASS_NAME, 
        JOB_CLASS, 
        SALARY_CLASS, 
        CASE 
            WHEN SALARY_CLASS = 'S' OR PAY_RATE > 1000 THEN (PAY_RATE / 2080) 
            ELSE PAY_RATE 
        END AS HourlyPayRate 
    FROM [LAWSONDWHR].[dbo].[RPT_EMPLOYEECENSUS_ASOF] 
    WHERE PROCESS_LEVEL = @PROCLEVEL 
        AND HRYEAR = @HRYEAR 
        AND HRPERIOD = @HRPERIOD 
        AND JOB_CLASS = 'RN' 
        AND FTE_TOTAL != 0 
        AND MASTER_EMP_STATUS NOT IN ('ZZ', 'T1')
)
-- 自动创建新表并插入平均值
SELECT AVG(HourlyPayRate) AS AvgHourlyPayRate
INTO dbo.NewPayRateAverageTable -- 这里替换成你想要的表名
FROM EmployeePayData;

情况2：新表已经存在（插入数据到现有表） #

如果已经提前创建了存储平均值的表（比如表结构有AvgHourlyPayRate、HRYEAR、HRPERIOD这些字段），就用INSERT INTO：

DECLARE @PROCLEVEL varchar(6), @HRYEAR varchar(5), @HRPERIOD varchar(15)
SET @PROCLEVEL = 'XXX'
SET @HRYEAR = '2019'
SET @HRPERIOD = '03'

WITH EmployeePayData AS (
    -- 同样的CTE内容
    SELECT 
        CASE 
            WHEN HRPERIOD = 3 THEN 'March' 
            WHEN HRPERIOD = 6 THEN 'June' 
            WHEN HRPERIOD = 9 THEN 'September' 
            WHEN HRPERIOD = 12 THEN 'DECEMBER' 
        END HRPERIOD, 
        EMPLOYEE, 
        HRYEAR, 
        PAY_RATE, 
        FTE_TOTAL, 
        JOB_CLASS_NAME, 
        JOB_CLASS, 
        SALARY_CLASS, 
        CASE 
            WHEN SALARY_CLASS = 'S' OR PAY_RATE > 1000 THEN (PAY_RATE / 2080) 
            ELSE PAY_RATE 
        END AS HourlyPayRate 
    FROM [LAWSONDWHR].[dbo].[RPT_EMPLOYEECENSUS_ASOF] 
    WHERE PROCESS_LEVEL = @PROCLEVEL 
        AND HRYEAR = @HRYEAR 
        AND HRPERIOD = @HRPERIOD 
        AND JOB_CLASS = 'RN' 
        AND FTE_TOTAL != 0 
        AND MASTER_EMP_STATUS NOT IN ('ZZ', 'T1')
)
-- 插入到已存在的表
INSERT INTO dbo.ExistingPayRateTable (AvgHourlyPayRate, HRYEAR, HRPERIOD) -- 替换成你的表字段
SELECT 
    AVG(HourlyPayRate) AS AvgHourlyPayRate,
    @HRYEAR,
    @HRPERIOD
FROM EmployeePayData;

• 如果你需要按不同维度（比如按HRPERIOD、JOB_CLASS）分组计算平均值，只需要在SELECT AVG(...)后面加GROUP BY 分组字段就行，比如GROUP BY HRPERIOD, HRYEAR。
• 注意PAY_RATE / 2080的除法，SQL里整数除法会取整，如果需要保留小数，可以转成浮点型，比如CAST(PAY_RATE AS FLOAT) / 2080，这样结果更准确。

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