dokuwiki

with date_hierarchy as (
select rownum rn,
       employee_id, 
       start_date, 
       end_date       
   from (select 
         employee_id, 
         start_date, 
         end_date from job_history
         order by employee_id, start_date)
)              
select d1.employee_id, 
       d1.start_date, 
       d1.end_date, 
       d2.start_date,
       d2.end_date,
       d1.end_date - d1.start_date aufenthalt,
       d1.end_date - d2.start_date
  from date_hierarchy d1 join date_hierarchy d2
   on (d1.employee_id = d2.employee_id and d1.rn = d2.rn - 1)
  -- where d1.employee_id = 176