• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 48
  • Last Modified:

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
0
Karen Schaefer
Asked:
Karen Schaefer
  • 3
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now