SQL Server / VBA删除从给定记录起X天内基于日期的“重复”记录？

Question

对于包含两个相关列( DateService和ActivityType )的记录集，我试图找到一种更高效、理想的纯Server解决方案，以在一定日期范围内查找和删除同一ActivityType的“重复”记录。例如，代码应该找到第一个ActivityType = 'X‘，然后从前一个记录中删除相同ActivityType的所有记录，其中DateService < 90天，然后在第一个记录之后找到相同ActivityType的下一个记录，删除所有类似的“重复”，其中DateService从该记录中删除90天，等等，直到到达ActivityType = 'X’的最后一个记录为止。

我在Access前端为数据库编写了像这样的过程VBA代码(我省略了很多周围的代码，因为这实际上不是一个VBA问题；dateRun的类型是Date，剪枝是Int类型，feeApp是DAO.Recordset)：

With feeApp
.MoveFirst
.FindFirst "[ActivityType] = 'IME-5'"
If Not .NoMatch Then
    dateRun = ![DateService]
    .FindNext "[ActivityType] = 'IME-5'"
    If Not .NoMatch Then
        Do While Not .NoMatch
            If ![DateService] <= dateRun + 30 Then
                dateRun = ![DateService]
                .Delete
                pruned = pruned + 1
            Else
                dateRun = ![DateService]
            End If
            .MovePrevious
            .FindNext "[ActivityType] = 'IME-5'"
        Loop
    End If
End If

这段代码运行得很好，非常慢，因为它一次只在记录集中爬行一次，在我的dbase中，这个代码块为不同的ActivityType运行了六次。

有人能给出如何在Server中解决这个问题的建议吗？我想我可以将这些代码块的一部分转换成单独的简单删除命令，例如，

DELETE * FROM tblFeeApp WHERE [ActivityType] = 'X' AND [DateService] >= #" & dateRun "# AND [DateService] <= #" & dateRun + 90 & "#"

但是，我仍然需要在VBA中运行查找操作，直到我为每个NoMatch获得一个ActivityType，这样我就不会认为它会更高效。我想知道是否没有一个纯粹的SQL解决方案，或者每个ActivityType可能只有一个SQL命令，我认为这些命令的效率仍然会提高数量级。

如果有人有任何建议，我们会非常感激和感谢您提前！

编辑

多亏了@Ben_Osborne，我想我几乎有了一个可行的解决方案。到目前为止，以存储过程的形式存在的问题是：

ALTER PROCEDURE [dbo].[procPruneFeeApp](@WCB nvarchar(255),@feeItem nvarchar(255), @daysApart Integer = 90)
AS

DELETE f1
from
    dbo.tblFeeApp f1
    join dbo.tblFeeApp f2 on
        f1.ActivityType = f2.ActivityType
        and DATEDIFF(d, f1.DateService, f2.DateService) between 0 and ABS(@daysApart - 1)
        and f1.Id < f2.Id
where
    f1.[WCB] = @WCB
    and f2.[WCB] =  @WCB
    and f1.[ActivityType] = @feeItem
    and f2.[ActivityType] = @feeItem
;

我用这个VBA函数调用proc：

Function pruneFeeApp(WCB As String, feeItem As String, Optional daysApart As Integer = 90) As Integer

If Not isNada(WCB) Then
    If Not isNada(feeItem) Then
        WCB = Replace("'" & WCB & "'", "''", "'")
        feeItem = Replace("'" & feeItem & "'", "''", "'")

        Dim qdef As DAO.QueryDef
        Set qdef = CurrentDb.CreateQueryDef("")
        qdef.ReturnsRecords = False
        qdef.Connect = "ODBC;" & getSQLstring
        qdef.SQL = "EXEC dbo.procPruneFeeApp @WCB = " & WCB & ", @feeItem = " & feeItem & ", @daysApart = " & daysApart
        qdef.Execute dbFailOnError

        pruneFeeApp = qdef.RecordsAffected
    End If
End If

pruneFeeApp = isNadaZ(pruneFeeApp, 0)

End Function

(这可能很明显，但isNada和isNadaZ是自定义函数，用于测试空值、空值、0值和其他自定义“无效”值；getSQLstring获取硬编码的SQL连接字符串；我为函数中的输入变量添加了替换函数，因为出于某种原因，我的表单将踢出包含'‘的字符串。)

这个过程似乎执行得很好，但到目前为止还没有影响到任何记录。我希望这可能是VBA函数而不是存储过程的问题。

编辑

表中的实际数据示例(省略不必要的列)：

Id WCB DateService ActivityType 1961 G0793728 6/23/2014 IME-5 1962 G0793728 6/26/2015 IME-5 1963 G0793728 8/6/2015 IME-5 1964 G0793728 6/4/2014 C-240 1965 G0793728 7/1/2014 C-240 1966 G0793728 2/25/2014 RFA-1LC 1967 G0793728 3/28/2014 RFA-1LC 1968 G0793728 3/31/2014 RFA-1LC

编辑

@Ben_Osborne下面的答案是有效的！只需要对使用ADODB的VBA过程调用进行一些调整，它的工作方式就像一种魅力。非常感谢所有的帮助！

Answer 1

如果我正确理解了需求，那么在90天内有另一个具有相同活动类型的记录时，应该删除记录。

我可能把事情简单化了。但如果没有，我会认为这是可行的：

    delete f1
    from
        dbo.tblFeeApp f1
        join dbo.tblFeeApp f2 on
            f1.ActivityType = f2.ActivityType
            and DATEDIFF(d, f1.DateofService, f2.DateofService) between 0 and 89 -- using this rather than "< 90" so that negative results are not included
            and f1.Id < f2.Id -- assuming you have an identity field.  Prevents a record from qualifying itself for deletion.  Also prevents multiple records with the same date of service from qualifying each other for deletion
    where
        f1.[WCB] = 'qualifying value' -- the record being deleted
        and f2.[WCB] = 'qualifying value' -- the latter recrd (not being deleted)
    ;

下面是一个SQL脚本，它模拟一些数据，然后使用这个逻辑删除它。在我的测试中，它总是找到要删除的记录。当然，您的实际数据将有所不同，但它是对删除逻辑的演示/测试：

    set nocount on;

    --mock up some wcb records
    declare @wbc table ([WCB] varchar(8));

    declare @w integer = 0;
    while @w < 10 begin
        insert into @wbc (WCB) values (left(newid(), 8));
        set @w = @w + 1;
    end;

    --select a random WCB record that will be the value that qualifies records for deletion
    declare @qualifyingWcb varchar(8)= (select top 1 wcb from @wbc order by newid());
    select [Qualifying WCB] = @qualifyingWcb;

    -- mock up some activity
    declare @tblFeeApp table (Id integer identity, [ActivityType] varchar(20), [DateofService] date, [WCB] varchar(100));

    declare @x integer = 0;
    while @x <= 10000 begin

        insert into @tblFeeApp (ActivityType, DateofService, WCB) 
        values
        (
            'IME-' + convert(varchar, convert(integer, rand() * 1000)), 
            dateadd(d, -1 * convert(integer, rand() * 200), getdate()),
            (select top 1 wcb from @wbc order by newid())
        );

        set @x = @x + 1;

    end;

    set nocount off;



    --delete f1
    select
        [What] = 'Record That Meet Deletion Criteria -->',
        f1.*,
        [Spacer] = '    ',
        [What] = 'The Record That Qualififes F1 for Deletion -->',
        f2.*
    from
        @tblFeeApp f1
        join @tblFeeApp f2 on
            f1.ActivityType = f2.ActivityType
            and DATEDIFF(d, f1.DateofService, f2.DateofService) between 0 and 89 -- using this rather than "< 90" so that negative results are not included
            and f1.Id < f2.Id -- assuming you have an identity field.  Prevents a record from qualifying itself for deletion.  Also prevents multiple records with the same date of service from qualifying each other for deletion
    where
        f1.[WCB] = @qualifyingWcb -- the record being deleted
        and f2.[WCB] = @qualifyingWcb -- the latter recrd (not being deleted)
    ;