Remove Duplicates where eff-to-date not equal to a specified date

How do I get the only those records for multiple rows of same EmpID where the Eff_to_date = 12/31/2049?

EMPID      NAME                    EFF_FROM_DATE      EFF_TO_DATE      CHANGE_DATE
2121      SMITH MARIE T      29-Mar-17              8-Sep-17                    28-Mar-17
6708      DAY, Jack              6-Jan-18                      31-Dec-49            4-Jan-18
6708      DAY, Jack                4-Jan-18                5-Jan-18             3-Jan-18
6919      PARK, HANGA J      20-Jan-18              26-Jan-18           19-Jan-18
6919      PARK, HANGA J      27-Jan-18              31-Dec-49           19-Jan-18
6919      PARK, HANGA J      2-Dec-17      1              9-Jan-18                    1-Jan-18
12880      JACKSON, MARIA      1-Jan-17                     31-Dec-49             1-Jan-18

results should be:

EMPID      NAME                    EFF_FROM_DATE      EFF_TO_DATE      CHANGE_DATE
6708      DAY, Jack              6-Jan-18                      31-Dec-49            4-Jan-18
6919      PARK, HANGA J      27-Jan-18              31-Dec-49           19-Jan-18
12880      JACKSON, MARIA      1-Jan-17                     31-Dec-49             1-Jan-18

thanks,

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
assuming the eff_to_Date column doesn't have time stamps populated:
select * from your_table where EFF_TO_DATE = to_date('12/31/2049','MM/DD/YYYY')

If time is there:
select * from your_table where EFF_TO_DATE >= to_date('12/31/2049','MM/DD/YYYY') and EFF_TO_DATE < to_date('12/31/2049','MM/DD/YYYY')+1
0
Jan LouwerensSoftware EngineerCommented:
From your example data, it look like simply this would work:

SELECT EMPID, NAME, EFF_FROM_DATE, EFF_TO_DATE, CHANGE_DATE FROM <Table_Name> WHERE
   TRUNC(EFF_TO_DATE) = TO_DATE('2049-12-31', 'YYYY-MM-DD')
ORDER BY EMPID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jan LouwerensSoftware EngineerCommented:
Oops, too late. My (late) comment is simply a restatement of slightwv's comment.
0
Jan LouwerensSoftware EngineerCommented:
Actually, slightwv's solutions is slightly better in the event there is an index on the EFF_TO_DATE column. Id there is an index, his solution would use the index, where as mine would not (since it's calling TRUNC() on that column, so couldn't use the index.)
0
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the quick response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.