Calculating Employee Service years

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

SELECT ppos.person_id,
       TRUNC (
          MONTHS_BETWEEN (
             COALESCE (ppos.projected_termination_date, SYSDATE),
             ppos.date_start)
          / 12)
       || ' Years '
       || TO_CHAR (
             FLOOR (
                MOD (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start),
                   12)))
       || ' Months '
       || (TO_DATE (COALESCE (ppos.projected_termination_date, SYSDATE))
           - ADD_MONTHS (
                ppos.date_start,
                TRUNC (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start))))
       || ' Days '
          yearOfService
  FROM per_periods_of_service ppos