Home » SQL & PL/SQL » SQL & PL/SQL » Flag field based on max(date) (9.2.0.6)
Flag field based on max(date) [message #335910] Thu, 24 July 2008 02:49 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
Need one help with an SQL. I am unable to think how to achieve this:

I have a table:

CREATE TABLE EMP_EXPERIENCE(EMPLOYEE_ID NUMBER, FROM_DATE DATE, TO_DATE DATE);



Inserted Few Records:

INSERT INTO EMP_EXPERIENCE VALUES(100, '01-JAN-2000', '31-DEC-2000');
INSERT INTO EMP_EXPERIENCE VALUES(100, '01-JAN-2001', '31-OCT-2005');
INSERT INTO EMP_EXPERIENCE VALUES(100, '01-NOV-2006', '10-DEC-2007');
INSERT INTO EMP_EXPERIENCE VALUES(200, '10-JAN-2003', '3-DEC-2005');
INSERT INTO EMP_EXPERIENCE VALUES(200, '4-DEC-2005', '31-OCT-2007');
INSERT INTO EMP_EXPERIENCE VALUES(300, '01-SEP-1999', '16-AUG-2006');

COMMIT;




I need a query which displays EMPLOYEE, FROM_DATE, TO_DATE and a Flag which indicates what was his latest employment based on the MAX(FROM_DATE). The output should look like:


EMPLOYEE_ID  FROM_DATE     TO_DATE LATEST_EMPLOYMENT
100	    11/1/2006	    12/10/2007	 Y
100	    1/1/2001	    10/31/2005	 N
100	    1/1/2000	    12/31/2000	 N
200	    12/4/2005	    10/31/2007	 Y
200	    1/10/2003	    12/3/2005	 N
300	    9/1/1999	    8/16/2006	 Y



Please help me with the query.

Thanks
Sandi
Re: Flag field based on max(date) [message #335914 is a reply to message #335910] Thu, 24 July 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ '01-JAN-2000' is NOT a date it is a string:
SQL> INSERT INTO EMP_EXPERIENCE VALUES(100, '01-JAN-2000', '31-DEC-2000');
INSERT INTO EMP_EXPERIENCE VALUES(100, '01-JAN-2000', '31-DEC-2000')
                                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


2/ Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Flag field based on max(date) [message #335923 is a reply to message #335914] Thu, 24 July 2008 03:01 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Michel,
I am just copy-pasting whatever I did, not sure why you got that error:

SQL> CREATE TABLE EMP_EXPERIENCE(EMPLOYEE_ID NUMBER, FROM_DATE DATE, TO_DATE DATE);

Table created.

SQL> INSERT INTO EMP_EXPERIENCE VALUES(100, '01-JAN-2000', '31-DEC-2000');

1 row created.

SQL> commit;

Commit complete.
Re: Flag field based on max(date) [message #335924 is a reply to message #335923] Thu, 24 July 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Carefully read what I posted: '01-JAN-2000' is NOT a date it is a string.
The fact it seems to work for you does not mean it is correct.
The fact it does not work for me PROVE it is NOT correct.
Use TO_DATE function.

Regards
Michel

[Updated on: Thu, 24 July 2008 03:04]

Report message to a moderator

Re: Flag field based on max(date) [message #335939 is a reply to message #335924] Thu, 24 July 2008 03:43 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank you Michel for updating me that. I'll remember that.
Re: Flag field based on max(date) [message #335954 is a reply to message #335910] Thu, 24 July 2008 04:47 Go to previous messageGo to next message
mfinn
Messages: 9
Registered: July 2008
Location: UK
Junior Member
SQL> SELECT ee.*,
  2         DECODE(ee.to_date,
  3                (SELECT MAX(ee2.to_date)
  4                   FROM emp_experience ee2
  5                  WHERE ee.employee_id = ee2.employee_id),'Y','N') latest_employment
  6    FROM emp_experience ee
  7  /

EMPLOYEE_ID FROM_DATE TO_DATE   L
----------- --------- --------- -
        100 01-JAN-00 31-DEC-00 N
        100 01-JAN-01 31-OCT-05 N
        100 01-NOV-06 10-DEC-07 Y
        200 10-JAN-03 03-DEC-05 N
        200 04-DEC-05 31-OCT-07 Y
        300 01-SEP-99 16-AUG-06 Y

6 rows selected.

SQL>


I strongly recommend that you don't call the column TO_DATE as it's a reserved word.
Re: Flag field based on max(date) [message #335966 is a reply to message #335954] Thu, 24 July 2008 05:02 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank you. Your query worked for me. Big help.

Regards,
Sandi
Re: Flag field based on max(date) [message #335969 is a reply to message #335954] Thu, 24 July 2008 05:07 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@mfinn

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


@sandhyaa

This query access many times the table, investigate the functions I pointed you.

Regards
Michel
Previous Topic: Exec Immediate question
Next Topic: SQL Query - help needed
Goto Forum:
  


Current Time: Sat Dec 10 09:22:31 CST 2016

Total time taken to generate the page: 0.07320 seconds