Select a.EEIDNO, a.Salary, e.Empdte, DATEDIFF(Day, e.Empdte, a.CUTOFFDTE),
Case
When (DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / 365.0 >= 1 Then
a.Salary
When (DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / 365.0 < 1 And e.Empdte <= '2017/06/30' Then
a.Salary * ((DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / (DATEDIFF(Day, '2017/01/01',
a.CUTOFFDTE) + 1))
Else
0
End As Discretionary_Bonus
From (SELECT '2017/12/31' AS CUTOFFDTE, a1.* FROM APPRAISAL a1) a, EEMASTER e
WHERE a.EFFECTDTE <= (SELECT MAX(z.EFFECTDTE)
FROM APPRAISAL z WHERE a.EEIDNO = z.EEIDNO AND z.EFFECTDTE <= a.CUTOFFDTE)
AND e.EEIDNO = a.EEIDNO AND (e.STATUS = 'A' OR (e.STATUS = 'T' AND e.LASTWDTE >
a.CUTOFFDTE))
order by e.EEIDNO
For example : When I change the year ('2017/12/31') to ('2018/12/31') the other two part can auto change the year just like ('2018/06/30' ) and ('2018/01/01')