SQL: How to create a table when the condition is that the data in two columns from two different row-CodePudding

I have a table of duplicates. I'd like to create a new table of duplicates where two columns: (1)start_date (2)return_date are the same. E.G. where start_date = return_date Issue: the start_date = return_date are on different rows for the same ID.

Data example:

ID  Start_Date   Return_date
A   11/1/21      12/15/21
A   12/15/21     12/20/21
B   11/1/21      12/22/22
B   12/25/22     12/30/22

In this example, I would want ID A to be pulled into my new dataset, because ID A has a start_date = return_date. (12/15/21)

Here's one examples of many that I've tried and failed.

proc sql;
create table new as
select distinct ID, start_date, return_date
from 
old_table
where (start_date = return_date);

CodePudding user response：

Your query will only return records where the start and return date are the same in the same record.

To get what you want you need to join the table with itself to be able to combine records with the same id. Then you will check that the start date of one of the tables matches any return date of the other table, filtered by the same id. Like:

select a.id, a.start_date, b.return_date
from old_table a inner join old_table b on a.id = b.id 
where a.start_date = b.return_date;

SQL Fiddle: http://sqlfiddle.com/#!9/353f12/1/0

Hope that helps