Including only one row in sql [closed]

What I’m trying to do is take these records that looks like this:

ID  NAME    STARTDATE   ENDDATE   EnrollmentMonth
1   PETER   20200705    20200729    20200701    
1   TONY    20200730    99991231    20200701

and change it to look like this:

ID  NAME    STARTDATE   ENDDATE   EnrollmentMonth
1   PETER   20190101    20200729    20200701 

Including the name that has enrollmented in 15th of the month, Peter enrolled in the 15th of July while Tony did not.

The idea is to pick only one name in July.

I am having difficult time making this work. Any help would be appreciated.

Thanks

Answer

You seem to want to filter rows where startdate is earlier than the 15th of the enrollment month:

select t.*
from mytable t
where t.startdate <= trunc(enrollmentmonth, 'month') + interval '15' month

If enrollmentmonth is always the first of a month, we can dispense trunc():

select t.*
from mytable t
where t.startdate <= enrollmentmonth + interval '15' month