sqlsql-serversql-server-2016

How to get Average Salary of SQL HourlyPayRate Column?


I am working on creating a SSRS report and I want to get the Average of the HourlyPayRate Column that I have(in my case statement). I am a self learner of SQL and was wondering how I can get the AVG of the HourlyPayRate in a new table(If even possible).

DECLARE @PROCLEVEL varchar(6),
                   @HRYEAR varchar(5),
                           @HRPERIOD varchar(15)
SET @PROCLEVEL = 'XXX'
SET @HRYEAR = '2019'
SET @HRPERIOD = '03'
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')
ORDER BY PAY_RATE

What I am doing is doing is when the Employee's Salary class is an 'S' for salary or if the pay rate is greater than 1000 then take the pay rate / 2080 the number of hours in a year.


Solution

  • Could you not just do some form of this? I have removed your variables and where clause but you can adjust as needed:

    Select Employee, AVG(HourlyPayRate)
    from (
    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]
    )
    GROUP BY Employee