Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to handle repeated records in SQL?

RajeshKotaJan 15 2021 — edited Jan 16 2021

Hello Community,
I have a simple data set like below:
image.pngI 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 :
image.pngThe 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:
image.png

This post has been answered by Frank Kulash on Jan 15 2021
Jump to Answer

Comments

Processing

Post Details

Added on Jan 15 2021
4 comments
145 views