Years of Service / Salary Query - Reports_to

Here is a sql/report that I needed to write to track years of service and salary info. (Set the values in red)


select distinct a.reports_to, a.emplid, b.name, (a.comprate * 26), a.grade, c.min_rt_annual, c.mid_rt_annual, c.max_rt_annual, a.sal_admin_plan ,E.HIRE_DT,
TO_CHAR(((SYSDATE - P.BIRTHDATE) / 365), 99) AGE
,TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - E.HIRE_DT),999999) TOTAL_DAYS_WORKED
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 5 THEN '5 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - 1825) * -1), 99999) END) TOTAL_DAYS_UNTIL_5_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 10 THEN '10 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 2)) * -1), 999999) END) TOTAL_DAYS_UNTIL_10_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 15 THEN '15 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 3)) * -1), 999999) END) TOTAL_DAYS_UNTIL_15_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 20 THEN '20 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 4)) * -1), 999999) END) TOTAL_DAYS_UNTIL_20_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 25 THEN '25 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 5)) * -1), 999999) END) TOTAL_DAYS_UNTIL_25_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 30 THEN '30 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 6)) * -1), 999999) END) TOTAL_DAYS_UNTIL_30_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 35 THEN '35 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 7)) * -1), 999999) END) TOTAL_DAYS_UNTIL_35_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 40 THEN '40 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 8)) * -1), 999999) END) TOTAL_DAYS_UNTIL_40_YRS_SERV
from ps_job a, ps_names b, ps_sal_grade_tbl c, ps_employment e, ps_person p
where a.reports_to in ('&reports_to')
and a.emplid = b.emplid
and a.emplid = e.emplid
and a.emplid = p.emplid
and b.name_type = 'PRI'
and b.effdt = (select max(b1.effdt) from ps_names b1
where b1.emplid = b.emplid
and b1.name_type = 'PRI'
and b1.effdt <= sysdate)
and c.setid = '&setid'
and c.grade = a.grade
and c.sal_admin_plan = a.sal_admin_plan
and c.effdt = (select max(c1.effdt) from ps_sal_grade_tbl c1
where c1.setid = c.setid
and c1.grade = c.grade
and c1.sal_admin_plan = c.sal_admin_plan
and c1.effdt <= sysdate)
and a.effdt = (select max(a1.effdt) from ps_job a1
where a1.emplid = a.emplid
and a1.empl_rcd = a.empl_rcd
and a1.effseq = a.effseq
and a1.effdt <= sysdate)
and a.effseq = (select max(a2.effseq ) from ps_job a2
where a2.emplid = a.emplid
and a2.empl_rcd = a.empl_rcd
and a2.effdt = a.effdt )
order by a.reports_to, total_years_worked desc, b.name