Forum Stats

  • 3,723,925 Users
  • 2,244,650 Discussions
  • 7,850,759 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

How to handle repeated records in SQL?

RajeshKota
RajeshKota Member Posts: 6 Green Ribbon
edited January 16 in SQL & PL/SQL

Hello Community,

I have a simple data set like below:

I need minimum start date & max end date of an employee whenever there is a dept id change or status code change.

So i wrote this formula for Min start date- select emp id, dept id, status code , min(start date) from tablename group by emp id, dept id, status code.

I've got the below result :

The highlighted dates are missing in my result because dept id and status code got repeated. Since i am taking min start date, its not considering those records.

Likewise, i need to get max end date also.

I hope i explained the problem.

Any help would be greatly appreciated.

Thanks.


Update - Adding sample data and expected ouptut.

create table employee(

emp_id int, start_date date, end_date date, dept_id int, status_code varchar(100));


insert into employee values

('2233','1/1/2020','1/25/2020','123','Active'),

('2233','1/25/2020','2/15/2020','123','Active'),

('2233','2/15/2020','3/29/2020','123','Active'),

('2233','3/29/2020','4/11/2020','456','Active'),

('2233','4/11/2020','5/5/2020','456','Leave'),

('2233','5/5/2020','6/17/2020','456','Active'),

('2233','6/17/2020','7/22/2020','456','Active'),

('2233','7/22/2020','8/19/2020','789','Active'),

('2233','8/19/2020','9/30/2020','789','Terminated'),

('2244','1/10/2020','1/25/2020','122','Active'),

('2244','1/25/2020','2/19/2020','122','Leave'),

('2244','2/19/2020','3/31/2020','122','Active'),

('2244','3/31/2020','4/11/2020','322','Active'),

('2244','4/11/2020','5/5/2020','322','Active'),

('2244','5/5/2020','6/17/2020','322','Active'),

('2244','6/17/2020','7/22/2020','322','Active'),

('2244','7/22/2020','8/24/2020','422','Active'),

('2244','8/24/2020','9/24/2020','422','Terminated')


Expected Output:


Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,207 Red Diamond
    edited January 15

    Hi,

    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 how you get those results from that data.

    What results do you want if end_date on one row is less than start_date of the next row for the same emp_id? What if end_date on one row is later than start_date of the next row? What if two rows have the same emp_id and also the same start_date? If these things are possible in your application, include examples in the sampe data, desired 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

  • RajeshKota
    RajeshKota Member Posts: 6 Green Ribbon

    Hey Frank,

    I've added sample data and expected output.

    Some of the keywords - Match_recognize, match are not working in my sql assistant. Can we try this using row number function?

    Thanks

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,207 Red Diamond
    edited January 16

    Hi, @RajeshKota

    Please don't change your messages after posting them, especially after other people have already replied; it makes reading the thread harder.

    create table employee(

    emp_id int, start_date date, end_date date, dept_id int, status_code varchar(100));


    insert into employee values

    ('2233','1/1/2020','1/25/2020','123','Active'),

    Don't try to insert VARCHAR2 values (such as '1/1/2020') into DATE columns (like start_date). Use TO_DATE, or DATE literals. Using the wrong data type is simply asking for trouble.

    1review the syntax of the INSERT statement. INSERT INTO employee VALUES ... adds only one row. Test (and, if necessary, fix) your statements before you post them.

    Some of the keywords - Match_recognize, match are not working in my sql assistant. 

    Tools are supposed to make things easier for you to do things. Why are you using a tool that makes things harder?

Sign In or Register to comment.