Click here to Skip to main content
14,240,765 members
Rate this:
Please Sign up or sign in to vote.
See more:
Removing Overlapping Date records in a Table based on 2 Columns

Below is my table

ID EMP_ID TEAM START DATE END DATE
01 0001 Team1 07/13/2018 01/01/3000
02 0001 Team2 06/01/2017 01/01/3000
03 0001 Team1 07/21/2018 01/01/3000
04 0001 Team2 08/07/2018 01/01/3000

I want to remove the overlapping date records:
03 0001 Team1 07/21/2018 01/01/3000
04 0001 Team2 08/07/2018 01/01/3000

Since for Team1 date range 07/21/2018-01/01/3000 already contained in 07/13/2018-01/01/3000.
And for Team2 date range 08/07/2018-01/01/3000 already contained in 6/01/2017-01/01/3000.

Urgent help and attention is required...

Thanks in Advance!

What I have tried:

Third party system is adding the duplicates in the DB
Posted
Updated 3-Aug-18 8:42am
Comments
Member 13934643 2-Aug-18 13:17pm
   
Thanks for the prompt response! No, I just want to keep earliest date. 01/01/3000 is considered end of time. So, the end date really doesn't matter. There can be many overlapping records (duplicates); keep the oldest starting date one. I believe we need to just manipulate the first query. Thanks again!
Rate this:
Please Sign up or sign in to vote.

Solution 1

Achieving this with simple query is going to be a difficult job. Rules are not very much clear. According to your data end date seems same, but start date is different and you want to keep the big date only. How many duplicates are going to be there? Just two?

Anyway,
-- you have all same end date with min start date; may be the query is not exactly right according to your final need
SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE;

-- what would happen if start date is same end date is different? let's pick the big date
SELECT ID, EMP_ID, [START DATE], MAX(END DATE) FROM  (SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE ) a GROUP BY ID, EMP_ID, [START DATE]

-- Now we are done with similar end date and similar start date
-- At this point I will write a function or take help of another tool, perhaps, Another programming language. 
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

If I've understood your requirements, something like this should work:
DELETE
FROM
    D
FROM
    my_table As D
WHERE
    Exists
    (
        SELECT 1
        FROM my_table As O
        WHERE O.TEAM = D.TEAM
        And O.[END DATE] > D.[START DATE]
        And 
        (
            O.[START DATE] < D.[START DATE] 
        Or 
            -- If tied on start date, keep the one with the earlier ID:
            (O.[START DATE] = D.[START DATE] And O.ID < D.ID)
        )
    )
;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100