How to handle repeated records in SQL?

RajeshKota

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:

Frank Kulash

Hi,

Depending on your exact requirements, data and version, you may want something like this:

SELECT  *
FROM   table_x
MATCH_RECOGNIZE
     (
       PARTITION BY emp_id
       ORDER BY   start_date
       MEASURES   MIN (start_date) AS min_start_date
       ,          MAX (end_date)   AS max_end_date
       ONE ROW PER MATCH
       PATTERN    (x+)
       DEFINE     x AS  dept_id     = FIRST (dept_id)
	            AND status_code = FIRST (status_code)
     )
ORDER BY emp_id, min_start_date
;

Of course, I can't test it without any sample data.

Frank Kulash

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

Frank Kulash

Hi,

Depending on your exact requirements, data and version, you may want something like this:

SELECT  *
FROM   table_x
MATCH_RECOGNIZE
     (
       PARTITION BY emp_id
       ORDER BY   start_date
       MEASURES   MIN (start_date) AS min_start_date
       ,          MAX (end_date)   AS max_end_date
       ONE ROW PER MATCH
       PATTERN    (x+)
       DEFINE     x AS  dept_id     = FIRST (dept_id)
	            AND status_code = FIRST (status_code)
     )
ORDER BY emp_id, min_start_date
;

Of course, I can't test it without any sample data.

RajeshKota

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

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?