Removing overlapping date records in a table based on 2 columns

Question

0.00/5 (No votes)

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 1-Aug-18 11:33am

Member 13934644

Updated 3-Aug-18 8:42am

Add a Solution

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!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mohibur Rashid · Answer 1 · 2018-08-01T16:41:00

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,

SQL

-- 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.

Richard Deeming · Answer 2 · 2018-08-03T08:43:00

If I've understood your requirements, something like this should work:

SQL

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)
        )
    )
;

Removing overlapping date records in a table based on 2 columns

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0