dokuwiki

select employee_id,
       first_name,
       last_name,
       department_id,       
       job_id,
       hire_date,        
       start_date,
       end_date,
       status
       from (
select employee_id,
       first_name,
       last_name,
       department_id,       
       job_id,
       hire_date,       
       (select max(end_date)+1 from job_history
           where employee_id = employees.employee_id) 
       start_date,
       null end_date,
       'aktuell' status,
       1 zeit
  from employees
union
select h.employee_id,
       first_name,
       last_name,
       h.department_id,
       h.job_id,
       hire_date,
       start_date,
       end_date,
       'historisch',
       0 zeit
from job_history h join employees e
  on h.employee_id = e.employee_id
) o 
where employee_id = 176
order by employee_id, 
         zeit,  
         start_date

select status, 
       job_id, 
       first_name, 
       last_name, 
       coalesce(start_date, 
          (select max(end_date)+1 
            from job_history where employee_id = fe.employee_id),
            hire_date) start_date,
       end_date
       from (
SELECT 'H' status,
       employees.employee_id,
       JOB_HISTORY.job_id, 
       EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       hire_date,
       start_date,
       end_date
FROM EMPLOYEES , JOB_HISTORY 
WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
union
select 'A',
       employees.employee_id,
       job_id, 
       first_name, 
       last_name, 
       hire_date,
       null,
       null 
from employees
) fe
--where last_name = 'Kochhar'
order by last_name, first_name, start_date

 select * from 
(
  select employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME  from employees

    minus

  SELECT  JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln
  FROM JOB_HISTORY , EMPLOYEES 
  WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
) 
--where id = 101


----

select * from (
SELECT 0 sb, 'history', start_date, end_date, JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln, JOB_HISTORY.JOB_ID jid
FROM JOB_HISTORY , EMPLOYEES 
WHERE  (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID)  
and JOB_HISTORY.EMPLOYEE_ID = :b_eid
union
select 1 sb, 'jetzt', null, sysdate, employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME, JOB_ID  from employees 
where employee_id = :b_eid
) order by sb, end_date


select sb, h, start_date, end_date, id, fn, ln, jid, prior jid from (
SELECT 0 sb, 'history' h, start_date, end_date, JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln, JOB_HISTORY.JOB_ID jid
FROM JOB_HISTORY , EMPLOYEES 
WHERE  (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID)  
and JOB_HISTORY.EMPLOYEE_ID = 101
union
select 1 sb, 'jetzt', null, sysdate, employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME, JOB_ID  from employees 
where employee_id = 101
) connect by PRIOR employee_id
 by sb, end_date

select employee_id as id, job_id, last_name, to_date(sysdate) from employees
union 
select employee_id, job_id, to_char(null), to_date(end_date) from job_history
order by 1,3,4 desc

select first_name, last_name from employees where employee_id in
(
  select employee_id
  from employees  
  where job_id = 'IT_PROG'
 union
  select employee_id
  from job_history 
  where job_id = 'IT_PROG'
)
------

select first_name, last_name, stand, datum from employees,  
(
  select employee_id, 
         job_id, 
         'jetzt' stand, 
         sysdate datum 
  from employees  
  where job_id = 'IT_PROG'
 union
  select employee_id, 
         job_id, 
         'früher' stand, 
         end_date datum 
  from job_history 
  where job_id = 'IT_PROG'
) history 
where history.employee_id = employees.employee_id

with history_union_employees as
(
SELECT 1 h, EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       JOB_HISTORY.START_DATE, 
       JOB_HISTORY.END_DATE, 
       JOB_HISTORY.JOB_ID 
FROM JOB_HISTORY , EMPLOYEES 
WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
union
select 2 h, EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       null,
       null,
       job_id       
from employees where employee_id in (select employee_id from job_history)
)
select first_name, last_name, start_date, end_date, job_title from 
 history_union_employees h join 
 jobs j on h.job_id = j.job_id
order by last_name, nvl(end_date,sysdate)

select status, 
       job_id, 
       first_name, 
       last_name, 
       coalesce(start_date, 
          (select max(end_date)+1 
            from job_history where employee_id = fe.employee_id),
            hire_date) start_date,
       end_date
       from (
SELECT 'H' status,
       employees.employee_id,
       JOB_HISTORY.job_id, 
       EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       hire_date,
       start_date,
       end_date
FROM EMPLOYEES , JOB_HISTORY 
WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
union
select 'A',
       employees.employee_id,
       job_id, 
       first_name, 
       last_name, 
       hire_date,
       null,
       null 
from employees
) fe
--where last_name = 'Kochhar'
order by last_name, first_name, end_date