period difference between 2 dates in emp table

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

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

That depends on ur Interest and Concentration.

hey

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

[QUOTE=rama_kota;22062]hey

how can we get those details using just the SQL Qerying.[/QUOTE]

The previous solution provided by our expert is SQL only.

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.

[QUOTE=indira.s;22533]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.[/QUOTE]

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