CODEWITHSUNDEEP
oracle-database
Durga Dutt
Durga Dutt

Reputation: 4113

Getting end date from start date

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.

Upvotes: 3

Views: 1234

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

You can use the LEAD analytic function for this

SELECT id,
       name,
       start_date,
       LEAD(start_date) over (partition by name
                                  order by start_date) end_date,
       post
  FROM emp

Presumably, though, in your real table, you have some sort of EMPLOYEE_ID that you can use rather than using the NAME to determine which rows go together. Otherwise, as soon as you have two employees named 'John Smith`, this will cause problems.

Upvotes: 3

arturro
arturro

Reputation: 1606

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;

Upvotes: 4

Related Questions