period difference between 2 dates in emp table

[jbabu]

Please can anybody send me the query to find the period between 2 employees in emp table based on hiredate.

[susarlasireesha]

Select Hiredate,lead(hiredate,1) over (order by hiredate) next_hiredate,round(months_between(lead(hiredate,1) over (order by hiredate),hiredate),3) Months from emp

[susarlasireesha]

That depends on ur Interest and Concentration.

[rama_kota]

hey

how can we get those details using just the SQL Qerying.

[debasisdas]

hey

how can we get those details using just the SQL Qerying.

The previous solution provided by our expert is SQL only.

[indira.s]

Hi,

The answer provided with LEAD and OVER gives you the difference between only successive employees.

If the question is to get difference between all employees, then its better to use a cross join and get difference between hire_dates:

select hire_date,startdate,(hire_date-startdate) diff
from employees e,
(select employee_id,hire_date startdate from employees) d
where e.employee_id<>d.employee_id;


Or, if its to get the difference between 2 specified dates:

select hire_date,startdate,
round(months_between(startdate,hire_date)) MonthsBetw
from employees,(select hire_date startdate from employees
where employee_id=&empid1)
where employee_id=&empid2;

Bye.

[susarlasireesha]

Hi,

The answer provided with LEAD and OVER gives you the difference between only successive employees.

If the question is to get difference between all employees, then its better to use a cross join and get difference between hire_dates:

select hire_date,startdate,(hire_date-startdate) diff
from employees e,
(select employee_id,hire_date startdate from employees) d
where e.employee_id<>d.employee_id;


Or, if its to get the difference between 2 specified dates:

select hire_date,startdate,
round(months_between(startdate,hire_date)) MonthsBetw
from employees,(select hire_date startdate from employees
where employee_id=&empid1)
where employee_id=&empid2;

Bye.

startdate is not a field in emp .The question is period between 2 employees in emp table based on hiredate.