CODEWITHSUNDEEP
sqloracle-databaseoracle11g
shivam
shivam

Reputation: 383

Calculate Years after Hire from same table

I have a requirement to calculate year of hire from a table, now sure how to achieve it

Below is case

enter image description here

and the Output should look like below based on the action column,

enter image description here

Any help would be greatly appreciated.

Many Thanks !

Upvotes: 0

Views: 42

Answers (2)

MT0
MT0

Reputation: 167972

You can use MAX with a CASE and GROUP By the person_number:

SELECT person_number,
       MONTHS_BETWEEN(
         SYSDATE,
         MAX( CASE WHEN action = 'HIRE' THEN effective_start_date END )
       ) / 12 AS Number_of_years
FROM   table_name
GROUP BY person_number;

So for your sample data:

CREATE TABLE table_name ( person_number, effective_start_date, action ) AS
SELECT 100, DATE '2000-07-01', 'HIRE' FROM DUAL UNION ALL
SELECT 100, DATE '2002-01-02', 'MANAGER CHANGE' FROM DUAL UNION ALL
SELECT 100, DATE '2014-06-20', 'PROMOTION' FROM DUAL UNION ALL
SELECT 100, DATE '2019-10-10', 'LOCATION CHANGE' FROM DUAL;

This outputs:

PERSON_NUMBER |                           NUMBER_OF_YEARS
------------: | ----------------------------------------:
          100 | 19.81079360190163281561131023496614894467

If you want full years then you can use TRUNC or FLOOR on the number of years and if you want to round to the nearest full year then use ROUND.

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Assuming the date column is really a date and not an absurd string, you can just filter:

select floor(months_between(sysdate, effective_start_date) / 12)
from t
where action = 'hire';

Upvotes: 1

Related Questions