Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Select service dates in 5 year intervals

    I want to select employees that have number of years service of 5 or greater and in intervals of 5 years..(5,10,15...etc, years of service)

    what operation can I do this in Oracle?

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Code is not real elegant, but it gets the job done:

    Code:
    SELECT '5 years or less' lastname, null firstname, null years_of_service FROM dual
    UNION ALL
    SELECT lastname, firstname, TRUNC( sysdate - hire_date ) / 365
    FROM employees
    WHERE TRUNC( sysdate - hire_date ) BETWEEN 0 AND (365*5)
    UNION ALL
    SELECT '6 year to 10 years' lastname, null, null FROM dual
    UNION ALL
    SELECT lastname, firstname, TRUNC( sysdate - hire_date ) / 365
    FROM employees
    WHERE TRUNC( sysdate - hire_date ) BETWEEN (365*6) AND (365*10)
    UNION ALL
    SELECT '11 year to 15 years' lastname, null, null FROM dual
    UNION ALL
    SELECT lastname, firstname, TRUNC( sysdate - hire_date ) / 365
    FROM employees
    WHERE TRUNC( sysdate - hire_date ) BETWEEN (365*11) AND (365*15)
    ...
    JoeB

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    select trunc((sysdate - a.hire_date))/365/5) * 5 from eployee a
    where trunc((sysdate - a.hire_date))/365/5) * 5 > 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •