Skip to Main Content
  • Questions

  • Use Analytical Functions to calculate years of experience.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yadnesh.

Asked: September 28, 2007 - 8:09 pm UTC

Last updated: October 01, 2007 - 7:04 am UTC

Version: 9.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,
I have a function which calculates years of experience of a person in a given organization. I have a table ACADEMIC_APPOINTMENT which is used to get the required output.

CREATE TABLE ACADEMIC_APPOINTMENT
(
UNVL_ID VARCHAR2(50 BYTE) NOT NULL,
FROM_DATE DATE NOT NULL,
INSTITUTION_CD VARCHAR2(30 BYTE) NOT NULL,
RANK_CD VARCHAR2(10 BYTE) NOT NULL,
THRU_DATE DATE
);

ALTER TABLE ACADEMIC_APPOINTMENT ADD (
CONSTRAINT PK_ACADEMIC_APPOINTMENT PRIMARY KEY (UNVL_ID, FROM_DATE, INSTITUTION_CD));

I need to find years this person is associated to a given institution "MyUniversity"

+ Null value in THRU_DATE indicates the person is still currently associated with that institution and that rank.
+ Person can be associated in the same university at multiple ranks at the same time, hence I need to count that duration only once.
eg: Person John is associated with MyUniversity with following data
Rank Manager
Start Date 1/1/2003
End Date 1/1/2005

Rank VP
Start Date 1/1/2004
End Date NULL

Hence his total experience is from 1/1/2003 till today. There could be gaps in between as well. I have written a function to calculate the years the person worked in MyUniversity using analytical functions (my first try with these functions), do you think I can avoid the loop at the end or is analytical function over here an over kill (not yet benchmarked)?.


CREATE OR REPLACE FUNCTION fetchYearsInMyUniversity(pUNVL_ID IN VARCHAR2) RETURN VARCHAR2
IS
lINSTITUTION_CD VARCHAR2(50):= 'MyUniversity';
lYEAR_COUNT NUMBER:=0;
CURSOR C1 IS
SELECT
new_from_date, new_thru_date,
nvl(MONTHS_BETWEEN(new_thru_date, new_from_Date),0)MONTH_COUNT
FROM
(
SELECT
prev_from_date, prev_thru_date,
from_Date, thru_Date,
CASE
--this is for the 1st record/row keep the values
WHEN FROM_DATE = PREV_FROM_DATE AND THRU_DATE = PREV_THRU_DATE THEN FROM_DATE
--take the prev thru date if from date overlaps previous record
WHEN FROM_DATE BETWEEN prev_from_date AND prev_thru_date THEN prev_thru_date
--when previous thru_date is sysdate then no more calc.
WHEN prev_thru_date >SYSDATE THEN NULL
ELSE from_date
END new_from_date,
CASE
--this is for the 1st record/row keep the values take the sysdate if thru>sysdate
WHEN FROM_DATE = PREV_FROM_DATE AND THRU_DATE = PREV_THRU_DATE THEN LEAST(THRU_DATE,SYSDATE)
--take the prev thru date if thru date overlaps previous record
WHEN thru_DATE BETWEEN prev_from_date AND prev_thru_date THEN prev_thru_date
WHEN thru_date>SYSDATE OR thru_date IS NULL THEN SYSDATE
WHEN prev_thru_date >SYSDATE THEN NULL
ELSE thru_date
END new_thru_date
FROM
(
SELECT from_Date, thru_Date,
DECODE(ROWNUM, 1, from_date, lag(from_Date,1) over(ORDER BY from_date)) prev_from_date,
DECODE(ROWNUM, 1, thru_date, NVL(lag(thru_Date,1) over(ORDER BY from_date),SYSDATE)) prev_thru_date
FROM ACADEMIC_APPOINTMENT
WHERE UNVL_ID = pUNVL_ID
AND INSTITUTION_CD='MyUniversity'
ORDER BY from_date
)
);
BEGIN
FOR C1_rec in c1
LOOP
lYEAR_COUNT := lYEAR_COUNT + C1_rec.MONTH_COUNT;
if (C1_rec.new_thru_date >=sysdate) then exit; end if;
END LOOP;

RETURN lYEAR_COUNT;
END;
/


Thanks
-Yadnesh

and Tom said...

seems like if you simply took your query (which I did not really look at at all) and

select sum(month_count)
from (YOUR_QUERY_HERE)
where new_thru_date < sysdate;


you would be done and would have no need for a function at all - but I'm not sure, since you have a procedural look that exits after some condition is met, but you apply this condition to data that is RANDOMLY SORTED - so you could accidentally one day get the first record have a large new_thru_date and ignoreing ALL OTHER RECORDS.

If the data was truly meant to be sorted and you correct that (adding the order by to your procedural code) - then my approach would be the same and you could just create a view and skip this function:

create view xxxx
as
select a.*, (select sum(month_count) from (YOUR_QUERY_HERE) where ...) time
from table a
/


the order by in the inline view you have seems rather "not useful", your decode in that inline view are confusing as well, seems like

nvl( lag()..., from_date)

would be less confusing.

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL solution

Michel Cadot, October 01, 2007 - 12:31 pm UTC


Here's an idea of what can be done in SQL:
SQL> alter session set nls_date_format='DD-MON-YYYY' nls_date_language=american;

Session altered.

SQL> drop table ACADEMIC_APPOINTMENT purge;

Table dropped.

SQL> create table ACADEMIC_APPOINTMENT as 
  2  select employee_id                             UNVL_ID, 
  3         add_months(start_date,100)+60*line      FROM_DATE,
  4  --       to_char(department_id)                  INSTITUTION_CD,
  5         'INSTITUTION_CD'                        INSTITUTION_CD,
  6         job_id                                  RANK_CD,
  7         case 
  8           when add_months(end_date,100)-30*line < sysdate
  9             then add_months(end_date,100)-30*line
 10           end                                   THRU_DATE
 11  from hr.JOB_HISTORY, 
 12       (select 0 line from dual union all select 1 from dual) data
 13  /

Table created.

SQL> select INSTITUTION_CD, UNVL_ID, FROM_DATE, THRU_DATE from ACADEMIC_APPOINTMENT order by 1, 2;
INSTITUTION_CD    UNVL_ID FROM_DATE   THRU_DATE
-------------- ---------- ----------- -----------
INSTITUTION_CD        101 22-MAR-1998 28-JAN-2002
INSTITUTION_CD        101 28-FEB-2002 15-JUL-2005
INSTITUTION_CD        101 21-JAN-1998 27-FEB-2002
INSTITUTION_CD        101 29-APR-2002 15-JUN-2005
INSTITUTION_CD        102 12-JUL-2001 25-OCT-2006
INSTITUTION_CD        102 13-MAY-2001 24-NOV-2006
INSTITUTION_CD        114 22-SEP-2006
INSTITUTION_CD        114 24-JUL-2006
INSTITUTION_CD        122 30-JUN-2007
INSTITUTION_CD        122 01-MAY-2007
INSTITUTION_CD        176 01-MAY-2007
INSTITUTION_CD        176 24-JUL-2006 30-APR-2007
INSTITUTION_CD        176 30-JUN-2007
INSTITUTION_CD        176 22-SEP-2006 31-MAR-2007
INSTITUTION_CD        200 17-JAN-1996 17-OCT-2001
INSTITUTION_CD        200 17-MAR-1996 17-SEP-2001
INSTITUTION_CD        200 31-DEC-2002 31-MAR-2007
INSTITUTION_CD        200 01-NOV-2002 30-APR-2007
INSTITUTION_CD        201 17-JUN-2004
INSTITUTION_CD        201 16-AUG-2004

20 rows selected.

SQL> with
  2    step1 as (
  3      select INSTITUTION_CD, UNVL_ID, FROM_DATE, nvl(THRU_DATE,sysdate) THRU_DATE,
  4             row_number ()  over (order by INSTITUTION_CD, UNVL_ID, FROM_DATE, THRU_DATE) rn,
  5             min(FROM_DATE) over (partition by INSTITUTION_CD, UNVL_ID 
  6                                  order by FROM_DATE, THRU_DATE) min_from_date, 
  7             max(nvl(THRU_DATE,sysdate)) over (partition by INSTITUTION_CD, UNVL_ID 
  8                                               order by FROM_DATE, THRU_DATE)
  9               max_thru_date
 10      from ACADEMIC_APPOINTMENT 
 11    ),
 12    step2 as (
 13      select INSTITUTION_CD, UNVL_ID, FROM_DATE, THRU_DATE,
 14             case 
 15               when from_date > 
 16                    nvl(lag(max_thru_date) over (partition by INSTITUTION_CD, UNVL_ID 
 17                                                 order by FROM_DATE, THRU_DATE)
 18                       ,from_date-1)
 19                 then from_date
 20               else min_from_date
 21             end min_from_date,
 22             max_thru_date,
 23             case 
 24               when from_date > 
 25                    nvl(lag(max_thru_date) over (partition by INSTITUTION_CD, UNVL_ID 
 26                                                 order by FROM_DATE, THRU_DATE)
 27                       , from_date-1)
 28                 then rn
 29             end grp
 30      from step1
 31    ),
 32    step3 as (
 33      select INSTITUTION_CD, UNVL_ID, FROM_DATE, THRU_DATE, min_from_date, max_thru_date,
 34             max(grp) over (partition by INSTITUTION_CD, UNVL_ID 
 35                            order by FROM_DATE, THRU_DATE) grp
 36      from step2
 37    )
 38  select INSTITUTION_CD, UNVL_ID, 
 39         max(min_from_date) min_from_date, max(max_thru_date) max_thru_date
 40  from step3
 41  group by INSTITUTION_CD, UNVL_ID, grp
 42  order by 1, 2, 3
 43  /
INSTITUTION_CD    UNVL_ID MIN_FROM_DA MAX_THRU_DA
-------------- ---------- ----------- -----------
INSTITUTION_CD        101 21-JAN-1998 27-FEB-2002
INSTITUTION_CD        101 28-FEB-2002 15-JUL-2005
INSTITUTION_CD        102 13-MAY-2001 24-NOV-2006
INSTITUTION_CD        114 24-JUL-2006 01-OCT-2007
INSTITUTION_CD        122 01-MAY-2007 01-OCT-2007
INSTITUTION_CD        176 24-JUL-2006 30-APR-2007
INSTITUTION_CD        176 01-MAY-2007 01-OCT-2007
INSTITUTION_CD        200 17-JAN-1996 17-OCT-2001
INSTITUTION_CD        200 01-NOV-2002 30-APR-2007
INSTITUTION_CD        201 17-JUN-2004 01-OCT-2007

10 rows selected.

But i'm not sure it works as it for all cases. Just a first draft.

Regards
Michel

Nopparat V., October 02, 2007 - 4:40 am UTC 

SQL> select unvl_id, min(from_date) from_date, max(thru_date) thru_date
  2  from (
  3    select unvl_id, from_date, thru_date
  4      , max (rn) over (partition by unvl_id order by from_date) grp
  5    from (
  6      select UNVL_ID, FROM_DATE, nvl(THRU_DATE,trunc(sysdate)) thru_date
  7        , case
  8            when from_date <= lag(nvl(THRU_DATE,trunc(sysdate)))
  9                                 over ( partition by unvl_id order by from_date )
 10            then null
 11            else row_number () over ( partition by unvl_id order by from_date )
 12          end rn
 13      from ACADEMIC_APPOINTMENT
 14    )
 15  )
 16  group by unvl_id, grp
 17  order by 1, 2
 18  /

   UNVL_ID FROM_DATE           THRU_DATE
---------- ------------------- -------------------
       101 21/01/1998 00:00:00 27/02/2002 00:00:00
       101 28/02/2002 00:00:00 15/07/2005 00:00:00
       102 13/05/2001 00:00:00 24/11/2006 00:00:00
       114 24/07/2006 00:00:00 02/10/2007 00:00:00
       122 01/05/2007 00:00:00 02/10/2007 00:00:00
       176 24/07/2006 00:00:00 30/04/2007 00:00:00
       176 01/05/2007 00:00:00 02/10/2007 00:00:00
       200 17/01/1996 00:00:00 17/10/2001 00:00:00
       200 01/11/2002 00:00:00 30/04/2007 00:00:00
       201 17/06/2004 00:00:00 02/10/2007 00:00:00

10 rows selected.

SQL>

Nopparat V., October 02, 2007 - 4:47 am UTC

Sorry, please ignore my above posts. I've forgotten some cases. Michel's version is better.

final

Nopparat V., October 02, 2007 - 4:58 am UTC 

SQL> update ACADEMIC_APPOINTMENT
  2  set from_date = to_date ('25/02/2002')
  3  where unvl_id = 101
  4    and from_date = to_date ('28/02/2002') ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select unvl_id, min(from_date) from_date, max(thru_date) thru_date
  2  from (
  3    select unvl_id, from_date, thru_date
  4      , max (rn) over (partition by unvl_id order by from_date) grp
  5    from (
  6      select UNVL_ID, FROM_DATE, nvl(THRU_DATE,trunc(sysdate)) thru_date
  7        , case
  8            when from_date <= max(nvl(THRU_DATE,trunc(sysdate)))
  9                                 over ( partition by unvl_id order by from_date
 10                                        rows between unbounded preceding and 1 preceding )
 11            then null
 12            else row_number () over ( partition by unvl_id order by from_date )
 13          end rn
 14      from ACADEMIC_APPOINTMENT
 15    )
 16  )
 17  group by unvl_id, grp
 18  order by 1, 2
 19  /

   UNVL_ID FROM_DATE           THRU_DATE
---------- ------------------- -------------------
       101 21/01/1998 00:00:00 15/07/2005 00:00:00
       102 13/05/2001 00:00:00 24/11/2006 00:00:00
       114 24/07/2006 00:00:00 02/10/2007 00:00:00
       122 01/05/2007 00:00:00 02/10/2007 00:00:00
       176 24/07/2006 00:00:00 30/04/2007 00:00:00
       176 01/05/2007 00:00:00 02/10/2007 00:00:00
       200 17/01/1996 00:00:00 17/10/2001 00:00:00
       200 01/11/2002 00:00:00 30/04/2007 00:00:00
       201 17/06/2004 00:00:00 02/10/2007 00:00:00

9 rows selected.

SQL>

Grant in Calgary, October 02, 2007 - 2:29 pm UTC

I didn't have a chance to look at this to closely but if I understand that you're looking to find out the cumulative total time that a person has spent at the institution, regardless of whether it was in one or more roles, this gives you a good base.

I don't believe that there is a need for analytics here.

Sorry I didn't have a chance to test the query but the design should be sound.

good luck.

(It still needs the logic to change days into years)


SELECT aa.unvl_id,
aa.institution_cd,
COUNT( DISTINCT dates.mydate ) days_at_institution
FROM academic_appointment aa,
( SELECT TO_DATE( '01-JAN-1900', 'DD-MON-YYYY' ) + ROWNUM - 1
mydate
FROM DUAL
CONNECT BY LEVEL <=
SYSDATE - TO_DATE( '01-JAN-1900', 'DD-MON-YYYY' )
+ 1 ) dates
WHERE dates.mydate BETWEEN aa.from_date
AND NVL( aa.thru_date, TRUNC( SYSDATE ))
GROUP BY aa.unvl_id,
aa.institution_cd;

Grant in Calgary, October 02, 2007 - 2:31 pm UTC

Let me know how it works out.

Napoleon, October 02, 2007 - 11:58 pm UTC

Here is my approach. Please comment if it works.

select unvl_id, institution_cd,
    max(nvl(thru_date, sysdate)),
    min(from_date),
    --
    -- select the largest period in record
    --
    months_between(max(nvl(thru_date, sysdate)), min(from_date)) -
    --
    -- deduct break periods
    --
    nvl((

          select
              sum(months_between(
  (
  --
  -- the earliest next employment period start date
  -- 
  select min(z.from_date) 
  from academic_appointment z 
  where x.unvl_id = z.unvl_id 
  and x.institution_cd = z.institution_cd 
  and z.from_date > x.thru_date),
              x.thru_date))
          from academic_appointment x
          where x.unvl_id = tt.unvl_id
          and x.institution_cd = tt.institution_cd
          and x.thru_date is not null
          and not exists (
   --
   -- exclude if thru date is in another employment period
   --
              select 1
              from academic_appointment y
              where x.unvl_id = y.unvl_id
              and x.institution_cd = y.institution_cd
              and x.from_date <> y.from_date
              and x.thru_date between y.from_date and nvl(y.thru_date, sysdate)
          )
          and exists (
   --
   -- ensure there is a from date that is later than thru date
   --
              select 1
              from academic_appointment z
              where x.unvl_id = z.unvl_id
              and x.institution_cd = z.institution_cd
              and z.from_date > x.thru_date
          )
          group by x.unvl_id, x.institution_cd
), 0) months_here
from academic_appointment tt
group by unvl_id, institution_cd

Vincent Malgrat, October 03, 2007 - 9:05 am UTC

I would go with 
SELECT unvl_id, institution_cd, SUM(days_between)
  FROM (SELECT unvl_id, from_date, institution_cd, rank_cd, thru_date,
                greatest(from_date,
                          nvl(MAX(thru_date + 1)
                              over(PARTITION BY unvl_id, a.institution_cd 
                                       ORDER BY thru_date, from_date 
                                   ROWS BETWEEN unbounded preceding 
                                            AND 1 preceding),
                              from_date)) start_date,
                --
                nvl(thru_date, trunc(SYSDATE + 1)) end_date,
                nvl(thru_date, trunc(SYSDATE + 1)) + 1 -
                 greatest(from_date,
                          nvl(MAX(thru_date + 1)
                              over(PARTITION BY unvl_id, a.institution_cd 
                                       ORDER BY thru_date, from_date 
                                   ROWS BETWEEN unbounded preceding 
                                            AND 1 preceding),
                              from_date)) days_between
           FROM academic_appointment a)
GROUP BY unvl_id, institution_cd


The idea is to count the days in a period only if we haven't count them already (the max with the order by gets the last end date of the periods we already took into account).

The "greatest" takes care of gaps between periods.

The primary key (which is a bit strange -- a person can not get two roles in the same institution starting the same day...) guarantees that I will not count a row twice since I included it in the partition/order.

Vincent Malgrat, October 03, 2007 - 10:13 am UTC

My approach doesn't work in all cases, please ignore my suggestion.

Grant in Calgary, October 11, 2007 - 1:09 pm UTC

This is the solution without the dates table.
It should work for all cases.
(The initial anti-join is to eliminate the records that are fully contained within another association. It makes this a great deal simpler)

SELECT unvl_id,
institution_cd,
from_date,
thru_date
FROM ( SELECT unvl_id,
institution_cd,
range_from_date from_date,
LAST_VALUE( range_thru_date ignore nulls ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY range_from_date DESC )
thru_date
FROM ( SELECT unvl_id,
institution_cd,
from_date,
thru_date,
CASE
WHEN LAG( thru_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
thru_date ) BETWEEN from_date AND thru_date
THEN NULL
ELSE from_date
END range_from_date,
CASE
WHEN LEAD( from_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
thru_date ) BETWEEN from_date AND thru_date
THEN NULL
ELSE thru_date
END range_thru_date
FROM ( SELECT aa.unvl_id,
aa.institution_cd,
aa.from_date,
NVL( aa.thru_date,
TO_DATE( '9999-dec-31', 'yyyy-mon-dd' ))
thru_date
FROM academic_appointment aa,
academic_appointment aa_b
WHERE aa.institution_cd = aa_b.institution_cd(+)
AND aa.unvl_id = aa_b.unvl_id(+)
AND aa.from_date >= aa_b.from_date(+)
AND aa.thru_date <= aa_b.thru_date(+)
AND aa.ROWID != aa_b.ROWID(+)
AND aa_b.ROWID IS NULL )))
WHERE from_date IS NOT NULL
ORDER BY 1,
2,
3,
4

Grant in Calgary, October 11, 2007 - 1:43 pm UTC 

Sorry,  
I made a mistake.  I'm a little trigger happy with that SEND button.

This is corrected and should give you the actual days and approximate months and years at the institution.

SQL> SELECT unvl_id,
                  institution_cd,
                  from_date,
                  thru_date
            FROM ( SELECT unvl_id,
                          institution_cd,
                          range_from_date from_date,
                          LAST_VALUE( range_thru_date ignore nulls ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY range_from_date DESC )
                                                                    thru_date
                    FROM ( SELECT unvl_id,
                                  institution_cd,
                                  from_date,
                                  thru_date,
                                  CASE
                                     WHEN LAG( thru_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) BETWEEN from_date
                                                          AND thru_date
                                     AND LAG( thru_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) IS NOT NULL
                                        THEN NULL
                                     ELSE from_date
                                  END range_from_date,
                                  CASE
                                     WHEN LEAD( from_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) BETWEEN from_date
                                                          AND thru_date
                                     AND LEAD( from_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) IS NOT NULL
                                        THEN NULL
                                     ELSE thru_date
                                  END range_thru_date
                            FROM ( SELECT aa.unvl_id,
                                          aa.institution_cd,
                                          aa.from_date,
                                          NVL( aa.thru_date, TRUNC( SYSDATE ))
                                                                    thru_date
                                    FROM academic_appointment aa,
                                         academic_appointment aa_b
                                   WHERE aa.institution_cd = aa_b.institution_cd(+)
                                     AND aa.unvl_id = aa_b.unvl_id(+)
                                     AND aa.from_date >= aa_b.from_date(+)
                                     AND NVL( aa.thru_date, TRUNC( SYSDATE )) <=
                                                   NVL( aa_b.thru_date(+),
                                                        TRUNC( SYSDATE ))
                                     AND aa.ROWID != aa_b.ROWID(+)
                                     AND aa_b.ROWID IS NULL )))
           WHERE from_date IS NOT NULL
ORDER BY 1,
         2,
         3,
         4

UNVL_ID    INSTITUTION_CD FROM_DATE THRU_DATE
---------- -------------- --------- ---------
101        INSTITUTION_CD 21-JAN-98 27-FEB-02
101        INSTITUTION_CD 28-FEB-02 15-JUL-05
102        INSTITUTION_CD 13-MAY-01 24-NOV-06
114        INSTITUTION_CD 24-JUL-06 11-OCT-07
122        INSTITUTION_CD 01-MAY-07 11-OCT-07
176        INSTITUTION_CD 24-JUL-06 30-APR-07
176        INSTITUTION_CD 01-MAY-07 11-OCT-07
200        INSTITUTION_CD 17-JAN-96 17-OCT-01
200        INSTITUTION_CD 01-NOV-02 30-APR-07
201        INSTITUTION_CD 17-JUN-04 11-OCT-07


10 rows selected.
SQL> SELECT   unvl_id,
         institution_cd,
         SUM( (thru_date - from_date) + 1 ) days,
         ROUND( SUM( (thru_date - from_date) + 1 ) /( 365 / 12 ), 1 ) months,
         ROUND( SUM( (thru_date - from_date) + 1 ) /( 365 ), 1 ) years
    FROM (
     SELECT unvl_id,
                  institution_cd,
                  from_date,
                  thru_date
            FROM ( SELECT unvl_id,
                          institution_cd,
                          range_from_date from_date,
                          LAST_VALUE( range_thru_date ignore nulls ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY range_from_date DESC )
                                                                    thru_date
                    FROM ( SELECT unvl_id,
                                  institution_cd,
                                  from_date,
                                  thru_date,
                                  CASE
                                     WHEN LAG( thru_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) BETWEEN from_date
                                                          AND thru_date
                                     AND LAG( thru_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) IS NOT NULL
                                        THEN NULL
                                     ELSE from_date
                                  END range_from_date,
                                  CASE
                                     WHEN LEAD( from_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) BETWEEN from_date
                                                          AND thru_date
                                     AND LEAD( from_date ) OVER( PARTITION BY unvl_id, institution_cd ORDER BY from_date,
                                          thru_date ) IS NOT NULL
                                        THEN NULL
                                     ELSE thru_date
                                  END range_thru_date
                            FROM ( SELECT aa.unvl_id,
                                          aa.institution_cd,
                                          aa.from_date,
                                          NVL( aa.thru_date, TRUNC( SYSDATE ))
                                                                    thru_date
                                    FROM academic_appointment aa,
                                         academic_appointment aa_b
                                   WHERE aa.institution_cd = aa_b.institution_cd(+)
                                     AND aa.unvl_id = aa_b.unvl_id(+)
                                     AND aa.from_date >= aa_b.from_date(+)
                                     AND NVL( aa.thru_date, TRUNC( SYSDATE )) <=
                                                   NVL( aa_b.thru_date(+),
                                                        TRUNC( SYSDATE ))
                                     AND aa.ROWID != aa_b.ROWID(+)
                                     AND aa_b.ROWID IS NULL )))
           WHERE from_date IS NOT NULL 
           )
GROUP BY unvl_id,
         institution_cd
ORDER BY 1,
         2,
         3,
         4

UNVL_ID    INSTITUTION_CD       DAYS     MONTHS      YEARS
---------- -------------- ---------- ---------- ----------
101        INSTITUTION_CD       2733       89.9        7.5
102        INSTITUTION_CD       2022       66.5        5.5
114        INSTITUTION_CD        445       14.6        1.2
122        INSTITUTION_CD        164        5.4         .4
176        INSTITUTION_CD        445       14.6        1.2
200        INSTITUTION_CD       3743      123.1       10.3
201        INSTITUTION_CD       1212       39.8        3.3


7 rows selected.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library