2017 calendar year or fiscal year 2016-2017 part time employees' gross salaries for the year

Transact-SQL
1

This is my query and I need help how to do calculation on gross salaries for the year for part time employee. I also have one issue I have is identifying employees who were PT in CY2017 and linking only to the wages earned as a PT employee. For example, there are employees who worked full-time during 2017, left the City’s employment, then returned in 2018 as part-time. If I identify these employees as PT(which they are today) and then pull wage data from CY2017 for them, it looks like they are making full-time wages as a PT employee.

PLEASE HELP ME WITH THIS QUERY. THANK YOU VERY MUCH

SELECT (RTRIM(LTRIM(RTRIM(a.FIRST_NAME)) + ' , ' + LTRIM(RTRIM(a.LAST_NAME))) + ' ' + LTRIM(RTRIM(a.MIDDLE_NAME))) as 'Full Name',
c.DESCRIPTION as Position,
convert(date,a.DATE_HIRED) as 'Start Date',
case when a.SALARY_CLASS = 'H' then 'Hourly' else 'Salary' end as 'Salary', a.PAY_RATE
from EMPLOYEE a inner join PAEMPLOYEE b
on a.EMPLOYEE = b.EMPLOYEE
inner join JOBCODE c
on a.JOB_CODE = c.JOB_CODE
where year(a.TERM_DATE) = '2017'
AND NBR_FTE = '0.5'
order by 'Full Name', a.DATE_HIRED

2

You'll need another table with actual hours worked to figure out gross salary, unless for this "part time" means exactly half of full-time, i.e., 1040 hours. Is that the situation, that the gross salary should be based on 1040 hours?

Btw, you should change the WHERE condition for TERM_DATE to make it "sargable":

WHERE a.TERM_DATE>= '20170101' AND a.TERM_DATE < '20180101'