Home » SQL & PL/SQL » SQL & PL/SQL » Query for Finding the Date History
Query for Finding the Date History [message #313415] Fri, 11 April 2008 18:06 Go to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

Hi Geeks,

My Table looks like this

Employee Assignment Position Job Grade Start_date End_date
101 132 2055 Clerk C01 10-Mar-2000 11-Feb-2001
101 132 2055 Clerk C02 12-Feb-2001 15-Dec-2001
101 132 2056 Clerk C02 16-Dec-2001 22-Jul-2003
101 132 2055 Clerk C03 23-Jul-2003 31-Dec-4000 --(unassigned)


Now I need output like

Employee Assignment Position Start_date End_date
101 132 2055 10-Mar-2000 15-Dec-2001
101 132 2056 16-Dec-2001 22-Jul-2003
101 132 2055 23-Jul-2003 31-Dec-4000 --(unassigned)

The following important things are to be considered
1. Rows to be grouped by position
2. Each row much have the start date and end date of the employee position. If the employee is the same postion and different grade or job that doesnt matter. But if the employee jumps from his position (2055) to 2056 and after sometime he comes to the same position (i.e. 2055) the date history should be separate.

I am unable to derive the logic also. I think we need to create a function for this.

Kindly help me out.

Thanks in Advance
Punithavel

[Updated on: Fri, 11 April 2008 18:14]

Report message to a moderator

Re: Query for Finding the Date History [message #313417 is a reply to message #313415] Fri, 11 April 2008 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SQL Experts
Advanced Oracle SQL questions - Complex queries, DML and DDL statements. Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!


>I think we need to create a function for this.
So, please proceed as planned.

[Updated on: Fri, 11 April 2008 22:27] by Moderator

Report message to a moderator

Re: Query for Finding the Date History [message #313427 is a reply to message #313415] Fri, 11 April 2008 23:58 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
One method would be to use the lag and lead analytic functions to identify separate lists of ordered starting and ending dates, then join them, as demonstrated below.

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

  EMPLOYEE ASSIGNMENT   POSITION JOB   GRADE START_DATE  END_DATE
---------- ---------- ---------- ----- ----- ----------- -----------
       101        132       2055 Clerk C01   10-Mar-2000 11-Feb-2001
       101        132       2055 Clerk C02   12-Feb-2001 15-Dec-2001
       101        132       2056 Clerk C02   16-Dec-2001 22-Jul-2003
       101        132       2055 Clerk C03   23-Jul-2003 31-Dec-4000

SCOTT@orcl_11g> WITH lag_and_lead AS
  2  	    (SELECT employee, assignment, position, start_date, end_date,
  3  		    LAG (position) OVER
  4  		      (PARTITION BY employee, assignment
  5  		       ORDER BY start_date) AS lag_position,
  6  		    LEAD (position) OVER
  7  		      (PARTITION BY employee, assignment
  8  		       ORDER BY start_date) AS lead_position
  9  	     FROM   your_table
 10  	     ORDER  BY employee, assignment, start_date),
 11  	  starts AS
 12  	    (SELECT employee, assignment, position, start_date, ROWNUM rn
 13  	     FROM   lag_and_lead
 14  	     WHERE  position <> lag_position
 15  	     OR     lag_position IS NULL),
 16  	  ends AS
 17  	    (SELECT employee, assignment, position, end_date, ROWNUM rn
 18  	     FROM   lag_and_lead
 19  	     WHERE  position <> lead_position
 20  	     OR     lead_position IS NULL)
 21  SELECT starts.employee, starts.assignment, starts.position, starts.start_date, ends.end_date
 22  FROM   starts, ends
 23  WHERE  starts.employee = ends.employee
 24  AND    starts.assignment = ends.assignment
 25  AND    starts.position = ends.position
 26  AND    starts.rn = ends.rn
 27  /

  EMPLOYEE ASSIGNMENT   POSITION START_DATE  END_DATE
---------- ---------- ---------- ----------- -----------
       101        132       2055 10-Mar-2000 15-Dec-2001
       101        132       2056 16-Dec-2001 22-Jul-2003
       101        132       2055 23-Jul-2003 31-Dec-4000

SCOTT@orcl_11g> 

Previous Topic: Cursor already open Error
Next Topic: DBMS_SCHEDULER
Goto Forum:
  


Current Time: Tue Dec 06 04:38:11 CST 2016

Total time taken to generate the page: 0.22305 seconds