基于2列删除表中的重叠日期记录 [英] Removing overlapping date records in a table based on 2 columns

问题描述

根据2列删除表格中的重叠日期记录



以下是我的表格



ID EMP_ID团队开始日期结束日期

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



我想删除重叠日期记录：

03 0001 Team1 07/21/2018 01/01/3000

04 0001 Team2 08 / 07/2018 01/01/3000



因为Team1日期07/21 / 2018-01 / 01/3000已包含在07/13 / 2018-01 / 01/3000。

和Team2日期08/07 / 2018-01 / 01/3000已经包含在6/01 / 2017-01 / 01/3000。



需要紧急帮助和关注...



提前致谢！



我尝试过：



第三方系统在DB中添加重复项

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

推荐答案

通过简单查询实现这一目标将是一项艰巨的任务。规则不是很明确。根据您的数据结束日期似乎相同，但开始日期不同，您只想保留大日期。那里会有多少重复？只有两个？



无论如何，

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. 

如果我理解了你的要求，这样的事情应该有效：

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

这篇关于基于2列删除表中的重叠日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！