Forum Stats

  • 3,767,863 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Getting the latest record

User_CC24U
User_CC24U Member Posts: 6 Green Ribbon

Hello Team,

I have an employee table with details like empno, name, join_date, termination_date, date_of_rehire, status. I am trying to achieve to get the least terminated record of latest record.

Lets say an employee hire and re-hire process went like below.

Here i need to fetch the with Trans_Date 19-Jun-20 as this is the least termination record of the latest.

I tried with  OVER (partition by employeeid order by Trans_date DESC) with but am getting the 18-May-18 record as the least and 09-Dec-20 as the latest but not desired one.

Can you please help out.


Thanks,

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi, @User_CC24U

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: How to Ask Questions in Developer Community Spaces - oracle-tech

    I am trying to achieve to get the least terminated record of latest record.

    What is a "record of a record"? What is a "record"?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    I tried with OVER (partition by employeeid order by Trans_date DESC) with but am getting the 18-May-18 record as the least and 09-Dec-20 as the latest but not desired one.

    Post the entire query. You might have just made a typo, or some other simple mistake.

  • User_CC24U
    User_CC24U Member Posts: 6 Green Ribbon

    Thanks Frank.

    I have attached the sample data here (not able to paste), below is the query used

    select * FROM (SELECT e.*,ROW_NUMBER() OVER (partition by employeeid order by TRANS_DATE DESC) r FROM idds.EXPORT_TABLE e) WHERE r = 1 and status='Terminated' and employeeid in('1134','1172','1215');


    Expected Output is:


    Thanks,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    Not everyone who wants to help you can or will open attachments. Post the sample data (CREATE TABLE and INSERT statements) right in a message, not in an attachment.

  • User_CC24U
    User_CC24U Member Posts: 6 Green Ribbon

    create table EXPORT_TABLE(

    EMPLOYEEID varchar2(10),

    name varchar2(100),

    trans_date date,

    join_date varchar2(10),

    date_of_rehire varchar2(10),

    termination_date varchar2(10),

    status varchar2(15));


    Inserts:



    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('18-FEB-21 07.05.36','DD-MON-RR HH24.MI.SS'),'10262017','11102020','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('17-FEB-21 17.05.27','DD-MON-RR HH24.MI.SS'),'10262017','11102020','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('22-JAN-21 15.08.07','DD-MON-RR HH24.MI.SS'),'10262017','11102020',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('11-NOV-20 23.16.22','DD-MON-RR HH24.MI.SS'),'10262017','11102020',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('10-JUL-19 13.06.11','DD-MON-RR HH24.MI.SS'),'10262017','10262017','06142019','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('15-JUN-19 17.05.43','DD-MON-RR HH24.MI.SS'),'10262017','10262017','06142019','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('12-JUN-19 21.05.35','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('12-JUN-19 09.05.45','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('31-OCT-17 23.08.24','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('30-OCT-17 17.06.24','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    create table EXPORT_TABLE(

    ...

    join_date varchar2(10),

    date_of_rehire varchar2(10),

    termination_date varchar2(10),

    That's a really bad idea. Information about dates should be kept in DATE columns. Storing date information in VARCHAR2 columns is simply asking for complicated code, inefficient queries, invalid data and run-time errors. Always use DATE columns for information about dates, like you did for trans_date. Given that you are using VARCHAR2 columns, why is the length 10, and not 8?

    You still haven't explained what a "record" is. If a "record" is a group of rows that all have the same employeeid and date_of_rehire, and you want to find the earliest (in order by trans_date) 'Terminated' row in the latest record that has any 'Terminated' rows, then you can do it like this:

    WITH  got_r  AS
    (
      SELECT   e.*
      ,        ROW_NUMBER () OVER ( PARTITION BY employeeid
      	  	    	        ORDER BY    SUBSTR (date_of_rehire, 5)   DESC
    				 ,	    LPAD (date_of_rehire, 8, '0') DESC
    				 ,   	    trans_date  	    
      	  	    	      ) AS r
      FROM     export_table e
      WHERE    status = 'Terminated'
    )
    SELECT    * -- or list all columns except r
    FROM	  got_r
    WHERE	  r = 1
    ORDER BY  employeeid
    ;
    

    Of course, if the only employeeid in the sample data is 1134, then you won't get either of the resul sets you posted.

    Notice that the query above is similar to what you tried. The query you posted excludes the unterminated rows after it computes r, so it gets the earliest row for each employeeid if and only if that row happens to be 'Terminated'. You want to exclude the unterminated rows first, and then compute r. Also, the query you posted makes no attempt to find the last "record".

  • User_CC24U
    User_CC24U Member Posts: 6 Green Ribbon

    HI Frank,

    Thanks for the reply, Details are below.

    create table EXPORT_TABLE(

    EMPLOYEEID varchar2(10),

    name varchar2(100),

    trans_date date,

    join_date varchar2(10),

    date_of_rehire varchar2(10),

    termination_date varchar2(10),

    status varchar2(15));


    Inserts

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('18-FEB-21 07.05.36','DD-MON-RR HH24.MI.SS'),'10262017','11102020','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('17-FEB-21 17.05.27','DD-MON-RR HH24.MI.SS'),'10262017','11102020','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('22-JAN-21 15.08.07','DD-MON-RR HH24.MI.SS'),'10262017','11102020',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('11-NOV-20 23.16.22','DD-MON-RR HH24.MI.SS'),'10262017','11102020',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('10-JUL-19 13.06.11','DD-MON-RR HH24.MI.SS'),'10262017','10262017','06142019','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('15-JUN-19 17.05.43','DD-MON-RR HH24.MI.SS'),'10262017','10262017','06142019','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('12-JUN-19 21.05.35','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('12-JUN-19 09.05.45','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('31-OCT-17 23.08.24','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1134','Aayu Bhat',to_date('30-OCT-17 17.06.24','DD-MON-RR HH24.MI.SS'),'10262017','10262017',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('18-FEB-21 07.05.22','DD-MON-RR HH24.MI.SS'),'06252018','08152020','01252021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('23-SEP-20 23.10.04','DD-MON-RR HH24.MI.SS'),'06252018','08152020','01252021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('17-AUG-20 11.08.02','DD-MON-RR HH24.MI.SS'),'06252018','08152020','01252021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('15-AUG-20 19.07.24','DD-MON-RR HH24.MI.SS'),'06252018','06252018','08142020','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('14-AUG-20 14.53.47','DD-MON-RR HH24.MI.SS'),'06252018','06252018',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('14-AUG-20 13.56.42','DD-MON-RR HH24.MI.SS'),'06252018','06252018',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('19-JUN-20 15.06.03','DD-MON-RR HH24.MI.SS'),'06252018','06252018',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1172','Michael Tam',to_date('26-MAY-20 15.07.10','DD-MON-RR HH24.MI.SS'),'06252018','06252018',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('18-FEB-21 07.05.36','DD-MON-RR HH24.MI.SS'),'05062019','02022021','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('13-FEB-21 09.05.36','DD-MON-RR HH24.MI.SS'),'05062019','02022021','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('10-JAN-21 08.09.36','DD-MON-RR HH24.MI.SS'),'05062019','02022021','02172021','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('17-FEB-21 21.05.25','DD-MON-RR HH24.MI.SS'),'05062019','02022021',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('03-FEB-21 23.07.48','DD-MON-RR HH24.MI.SS'),'05062019','02022021',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('02-MAR-20 19.10.35','DD-MON-RR HH24.MI.SS'),'05062019','05062019','02282020','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('13-JAN-20 19.07.51','DD-MON-RR HH24.MI.SS'),'05062019','05062019','02282020','Terminated');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('25-JUN-19 13.05.49','DD-MON-RR HH24.MI.SS'),'05062019','05062019',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('18-JUN-19 13.05.41','DD-MON-RR HH24.MI.SS'),'05062019','05062019',null,'Active');

    Insert into EXPORT_TABLE (EMPLOYEEID,NAME,TRANS_DATE,JOIN_DATE,DATE_OF_REHIRE,TERMINATION_DATE,STATUS) values ('1215','Mark Stan',to_date('06-MAY-19 07.05.55','DD-MON-RR HH24.MI.SS'),'05062019','05062019',null,'Active');


    Query Used:

    select * FROM (SELECT e.*,ROW_NUMBER() OVER (partition by employeeid order by TRANS_DATE DESC) r FROM EXPORT_TABLE e) WHERE r = 1 and status='Terminated' and employeeid in('1134','1172','1215');

    Expected Out:


    Thanks,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    So, my guess about what you want was wrong. That's not surprising; guessing isn't a good way to solve problems.

    If you can explain what you want to do, then someone can help you do it. If you use any non-standard terms, like "record", in your explanation, then say clearly what they mean.

  • User_CC24U
    User_CC24U Member Posts: 6 Green Ribbon

    Thanks Frank, noted.