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.
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