Getting end date from start date

Question

I have a EMP table in my database with the following fields: ``` id,name,post,joining_date. ``` Whenever any employees designation changes, a new row inserted with new designation. Means there are multiple rows for an employee if he got any promotion. Now i want to get end date designation wise for each employee.Like if my date is like : ``` id name start_date post 1 x 24-JAN-05 Software Engineer 2 y 23-JAN-08 Associate Software Engineer 3 x 26-Aug-05 Senior Sofware Engineer 4 y 20-FEB-09 Sofware Engineer ``` I want data in following format : ``` id name start_date end_date post 1 x 24-JAN-05 26-Aug-05 Software Engineer 2 x 26-Aug-05 null Senior Software Engineer 3 y 23-JAN-08 20-FEB-09 Associate Software Engineer 4 y 20-FEB-09 null Software Engineer ``` So I want to get data with end date for each designation for each employee. I dont want to use PL/SQL. I want to use only query.I hope it makes sense.

Accepted Answer

Here we go:

```
select id, name, start_date, lead(start_date) over (partition by name
order by start_date) as end_date, post from emp;
```

Getting end date from start date

Solution

Problem