Home » SQL & PL/SQL » SQL & PL/SQL » Need help on sorting dates in a column
Need help on sorting dates in a column [message #194567] Fri, 22 September 2006 12:47 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
I have the following tabel below that keeps track of an employee's job location. This example only shows one employee_id but assume more.

JOBID	DATE	        LOCATION	employee_id
3521	11-19-997	FACTORY	        6102
3520	07-16-998	FACTORY	        6102
3524	01-26-002	FACTORY	        6102
3525	10-03-002	FACTORY	        6102
3526	05-18-003	FIELD	        6102
3527	05-24-003	FIELD	        6102
3528	05-27-003	VACATION	6102
3529	05-28-003	FACTORY 	6102
3530	05-28-003	FIELD	        6102
3541	05-30-003	FIELD	        6102


I want to create a view that adds a new column to which has the job_id of the last FACTORY location the the employee worked at. So my view shoul look like this.

JOBID	DATE	        LOCATION	employee_id  Last_FactoryID
3521	11-19-997	FACTORY	        6102	
3520	07-16-998	FACTORY	        6102	
3524	10-03-002	FACTORY	        6102	
3525	10-03-002	FACTORY	        6102	
3526	05-18-003	FIELD	        6102	        3525
3527	05-24-003	FIELD	        6102	        3525
3528	05-27-003	VACATION	6102            3525	
3529	05-28-003	FACTORY	        6102	
3530	05-28-003	FIELD	        6102	        3529
3541	05-30-003	FIELD	        6102	        3529


I tried doing the following.

select
E.*,
(select E3.job_id from employee E3 where E3.date =
(select max(E2.date) from employee E2
where E2.employee_id = E.employee_id
and E2.date < E.date)) as Last_FactoryID

from
employee E

I'm running into the problem where i my sub query is returning more than one row since it's possible for an employee to work on more than one job on a given date.

Is there a better way to do this? Thanks for the help.




icon2.gif  Re: Need help on sorting dates in a column [message #194611 is a reply to message #194567] Sat, 23 September 2006 00:35 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
did you try using having clause?
Re: Need help on sorting dates in a column [message #194613 is a reply to message #194567] Sat, 23 September 2006 00:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Try like this ... .

SQL> select * from emp_job;

     JOBID DATE_D    LOCATION   EMPLOYEE_ID
---------- --------- ---------- -----------
      3541 30-MAY-03 FIELD             6102
      3520 16-JUL-98 FACTORY           6102
      3521 19-NOV-97 FACTORY           6102
      3524 26-JAN-02 FACTORY           6102
      3530 28-MAY-03 FIELD             6102
      3526 18-MAY-03 FIELD             6102
      3529 28-MAY-03 FACTORY           6102
      3525 03-OCT-02 FACTORY           6102
      3527 24-MAY-03 FIELD             6102
      3528 27-MAY-03 VACATION          6102

10 rows selected.

SQL> SELECT E.* ,
  2       (SELECT DISTINCT
  3       DECODE(e.LOCATION,'FACTORY',NULL,
  4       MAX(JOBID) KEEP (DENSE_RANK LAST ORDER BY DATE_D,JOBID)
  5       OVER (PARTITION BY LOCATION)) FROM
  6       emp_job e1
  7       WHERE e.DATE_D >= e1.DATE_D
  8       AND e.EMPLOYEE_ID = e1.EMPLOYEE_ID
  9       AND e1.LOCATION ='FACTORY') lst_fact
 10   FROM emp_job e
 11   ORDER BY 1
 12  ;

     JOBID DATE_D    LOCATION   EMPLOYEE_ID LST_FACT
---------- --------- ---------- ----------- ----------
      3520 16-JUL-98 FACTORY           6102
      3521 19-NOV-97 FACTORY           6102
      3524 26-JAN-02 FACTORY           6102
      3525 03-OCT-02 FACTORY           6102
      3526 18-MAY-03 FIELD             6102 3525
      3527 24-MAY-03 FIELD             6102 3525
      3528 27-MAY-03 VACATION          6102 3525
      3529 28-MAY-03 FACTORY           6102
      3530 28-MAY-03 FIELD             6102 3529
      3541 30-MAY-03 FIELD             6102 3529

10 rows selected.

SQL>


Thumbs Up
Rajuvan.
Re: Need help on sorting dates in a column [message #194617 is a reply to message #194567] Sat, 23 September 2006 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


OR LIKE ..

 SELECT E.*,
 		(SELECT DECODE(e.LOCATION,'FACTORY',NULL,MAX( E3.jobid)) 
		 FROM emp_job  E3 
		 WHERE E3.DATE_d =
		 	   (SELECT MAX(E2.DATE_d) FROM emp_job E2 
			   WHERE E2.employee_id = E.employee_id 
			   AND E2.DATE_d <= E.DATE_d AND
		 e2.LOCATION ='FACTORY')
		AND  e3.LOCATION ='FACTORY') AS Last_FactoryID
FROM
emp_job  E  ORDER BY 1


Thumbs Up
Rajuvan.
Re: Need help on sorting dates in a column [message #194821 is a reply to message #194567] Mon, 25 September 2006 09:04 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Rustican wrote on Fri, 22 September 2006 13:47


JOBID	DATE	        LOCATION	employee_id
3521	11-19-997	FACTORY	        6102
3520	07-16-998	FACTORY	        6102
3524	01-26-002	FACTORY	        6102
3525	10-03-002	FACTORY	        6102




Why would anyone want to display date as such?
Re: Need help on sorting dates in a column [message #194865 is a reply to message #194613] Mon, 25 September 2006 15:18 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Thanks for the help. i'm trying to follow what your're doing and i'm a bit lost as to what the internal query is doing.

  2       (SELECT DISTINCT
  3       DECODE(e.LOCATION,'FACTORY',NULL,
  4       MAX(JOBID) KEEP (DENSE_RANK LAST ORDER BY DATE_D,JOBID)
  5       OVER (PARTITION BY LOCATION)) FROM
  6       emp_job e1
  7       WHERE e.DATE_D >= e1.DATE_D
  8       AND e.EMPLOYEE_ID = e1.EMPLOYEE_ID
  9       AND e1.LOCATION ='FACTORY') lst_fact



I understand that you're creating a new column named 'LAST_FAC' and leaving the field null if the location was a 'FACTORY' value. I'm not sure how the default value you're setting works though.

I'm not familiar with the 'KEEP' keyword. What is it doing?



rajavu1 wrote on Sat, 23 September 2006 00:46


Try like this ... .

SQL> select * from emp_job;

     JOBID DATE_D    LOCATION   EMPLOYEE_ID
---------- --------- ---------- -----------
      3541 30-MAY-03 FIELD             6102
      3520 16-JUL-98 FACTORY           6102
      3521 19-NOV-97 FACTORY           6102
      3524 26-JAN-02 FACTORY           6102
      3530 28-MAY-03 FIELD             6102
      3526 18-MAY-03 FIELD             6102
      3529 28-MAY-03 FACTORY           6102
      3525 03-OCT-02 FACTORY           6102
      3527 24-MAY-03 FIELD             6102
      3528 27-MAY-03 VACATION          6102

10 rows selected.

SQL> SELECT E.* ,
  2       (SELECT DISTINCT
  3       DECODE(e.LOCATION,'FACTORY',NULL,
  4       MAX(JOBID) KEEP (DENSE_RANK LAST ORDER BY DATE_D,JOBID)
  5       OVER (PARTITION BY LOCATION)) FROM
  6       emp_job e1
  7       WHERE e.DATE_D >= e1.DATE_D
  8       AND e.EMPLOYEE_ID = e1.EMPLOYEE_ID
  9       AND e1.LOCATION ='FACTORY') lst_fact
 10   FROM emp_job e
 11   ORDER BY 1
 12  ;

     JOBID DATE_D    LOCATION   EMPLOYEE_ID LST_FACT
---------- --------- ---------- ----------- ----------
      3520 16-JUL-98 FACTORY           6102
      3521 19-NOV-97 FACTORY           6102
      3524 26-JAN-02 FACTORY           6102
      3525 03-OCT-02 FACTORY           6102
      3526 18-MAY-03 FIELD             6102 3525
      3527 24-MAY-03 FIELD             6102 3525
      3528 27-MAY-03 VACATION          6102 3525
      3529 28-MAY-03 FACTORY           6102
      3530 28-MAY-03 FIELD             6102 3529
      3541 30-MAY-03 FIELD             6102 3529

10 rows selected.

SQL>


Thumbs Up
Rajuvan.

Re: Need help on sorting dates in a column [message #194866 is a reply to message #194821] Mon, 25 September 2006 15:19 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
That was a mistake on my part when i was formatting the date. I typed in MM-DD-YYY instead of MM-DD-YYYY. Thanks for the catch.

joy_division wrote on Mon, 25 September 2006 09:04

Rustican wrote on Fri, 22 September 2006 13:47


JOBID	DATE	        LOCATION	employee_id
3521	11-19-997	FACTORY	        6102
3520	07-16-998	FACTORY	        6102
3524	01-26-002	FACTORY	        6102
3525	10-03-002	FACTORY	        6102




Why would anyone want to display date as such?

Re: Need help on sorting dates in a column [message #195417 is a reply to message #194866] Thu, 28 September 2006 08:57 Go to previous message
Rustican
Messages: 51
Registered: July 2006
Member
Is there a way that i can use the dense rank function to add a new column that can link up the last FACTORY time to the FIELD or VACATION fields.


So i start out with a table like this:

     JOBID DATE_D    LOCATION   EMPLOYEE_ID 
---------- --------- ---------- ----------- 
      3520 16-JUL-98 FACTORY           6102
      3521 19-NOV-97 FACTORY           6102
      3524 26-JAN-02 FACTORY           6102
      3525 03-OCT-02 FACTORY           6102
      3526 18-MAY-03 FIELD             6102 
      3527 24-MAY-03 FIELD             6102 
      3528 27-MAY-03 VACATION          6102 
      3529 28-MAY-03 FACTORY           6102
      3530 28-MAY-03 FIELD             6102 
      3541 30-MAY-03 FIELD             6102 



And end up with something like this:


     JOBID DATE_D    LOCATION   EMPLOYEE_ID LST_FACT
---------- --------- ---------- ----------- ----------
      3520 16-JUL-97 FACTORY           6102 1
      3521 19-NOV-98 FACTORY           6102 2
      3524 26-JAN-02 FACTORY           6102 3
      3525 03-OCT-02 FACTORY           6102 4
      3526 18-MAY-03 FIELD             6102 4
      3527 24-MAY-03 FIELD             6102 4
      3528 27-MAY-03 VACATION          6102 4
      3529 28-MAY-03 FACTORY           6102 5
      3530 28-MAY-03 FIELD             6102 5
      3541 30-MAY-03 FIELD             6102 5


I tried using the dense_rank() function.

select
E.*,
dense_rank() over (partition by location order by Date)
from employee


But that's not working since it's ranking by all the location types and not just the 'FACTORY'
Previous Topic: insert into two tables from one query
Next Topic: Notify when message arrives in multi-consumer queue in Oracle 8i
Goto Forum:
  


Current Time: Fri Dec 09 23:24:28 CST 2016

Total time taken to generate the page: 0.05847 seconds