I'm using the current case statement as an expression in my query to find service anniversaries. The why it's working now is if I run it anytime during September it's gives me employees whose anniversary is during September. I need to change the case statement so that when I run it in September, it gives me list of employees whose anniversary is in October. When I run it in October, I should get employee's whose service anniversary is in November.
Any help would be appreciated.
CASE
WHEN (ADD_MONTHS(B.SERVICE_DT, 60)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 5
WHEN (ADD_MONTHS(B.SERVICE_DT, 120)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 10
WHEN (ADD_MONTHS(B.SERVICE_DT, 180)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 15
WHEN (ADD_MONTHS(B.SERVICE_DT, 240)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 20
WHEN (ADD_MONTHS(B.SERVICE_DT, 300)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 25
WHEN (ADD_MONTHS(B.SERVICE_DT, 360)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 30
WHEN (ADD_MONTHS(B.SERVICE_DT, 420)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 35
WHEN (ADD_MONTHS(B.SERVICE_DT, 480)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 40
WHEN (ADD_MONTHS(B.SERVICE_DT, 540)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 45
WHEN (ADD_MONTHS(B.SERVICE_DT, 600)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 50
WHEN (ADD_MONTHS(B.SERVICE_DT, 660)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 55
WHEN (ADD_MONTHS(B.SERVICE_DT, 720)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 60
WHEN (ADD_MONTHS(B.SERVICE_DT, 780)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 65
WHEN (ADD_MONTHS(B.SERVICE_DT, 840)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 70
ELSE 0 END