QUERY SQL ORACLE-CodePudding

I'm trying to solve this question "Make a query that shows the name and the worker's bonus based on the years working in the company: If he takes between 1 and 5 years, he will be given 100 euros. If he has between 6 and 10 years, he will be given 50 euros per year. If he has between 11 and 20 years, he will be given 70 euros per year. If he has more than 21 years, he will be given 100 euros per year"

Thats what I tried... I have a table (named workers) with the information of them (admissiondate, name...)

  SELECT name, (EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate))*(CASE EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)
    WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)>21 THEN 100
    WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)<=21 AND AND EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)>10 THEN 70
    WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)<=10 AND EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM admissiondate)>4 THEN 50
    ELSE 100 END) AS Gratification
    FROM workers;

But it doesn't work. so, please, can you advice me anything useful?

CodePudding user response：

Thanks all of you. I think I have founded the solution.

SELECT name, CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)>21 THEN 100*TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)<=21 AND TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)>10 THEN 70*TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12) 
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)<=10 AND TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)>4 THEN 50*TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12)
ELSE 100*TRUNC(MONTHS_BETWEEN(SYSDATE, admissiondate)/12) END AS "Gratification €"
    FROM workers
    ORDER BY name;