There may be a very simple way to do this, but I can't quite think of it -- I have a dataset that returns a minimum job title and minimum effective date, then all effdts > than the min_effdt. In order to use this data in a charting program, I would like to rank each successive effdt if it exists, as in Min Role Effdt, then 2nd, 3rd, Max. Of course there could be anywhere from 2 to 20 jobs per person.
At first I considered trying a case statement, but I don't think that works when analyzing two columns at once. Is there a SQL statement that will allow ranking? Right now my data looks like
Employee Number | Min Base Role | Min Role Effdt | Base Role | Role Effdt
and comes from two tables, with the 2nd table brought in twice to get the Role / Effdt as Min, then All greater than Min.
I am using ORACLE. Code is below:
SELECT DISTINCT AL4.FULL_NAME, AL4.EMPLOYEE_NUMBER, AL4.HIRE_DATE, AL4.DATE_OF_BIRTH, AL4.AGE, AL4.TERM_DATE, AL4.ETHNIC_ORIGIN, AL2.RECORDVALUE AS MIN_BASE_ROLE, AL3.RECORDVALUE AS BASE_ROLE, AL3.EFFECTIVE_START_DATE AS "ROLE EFFECTIVE DATE", AL2.EFFECTIVE_START_DATE AS "MIN ROLE EFFDT" FROM T1 AL2, T2 AL3, T3 AL4WHERE AL4.PERSON_ID = AL2.PERSON_IDAND AL4.PERSON_ID = AL3.PERSON_IDAND AL4.EMPLOYEE_NUMBER = AL2.HISL_IDAND AL4.EMPLOYEE_NUMBER = AL3.HISL_IDAND AL2.RECORDTYPE = 'BASE_ROLE'AND AL3.RECORDTYPE = 'BASE_ROLE'AND AL2.EFFECTIVE_START_DATE = (SELECT MIN(A.EFFECTIVE_START_DATE) from T1 A where A.person_id = al2.person_id and a.recordtype = al2.recordtype)AND AL3.EFFECTIVE_START_DATE > AL2.EFFECTIVE_START_DATE AND (AL4.TERM_DATE >= '01-JAN-2012' or AL4.TERM_DATE is NULL)order by AL4.EMPLOYEE_NUMBER