基于类型和日期排除记录的SQL查询

Question

原始

 match_date actual_date      colA        type   type_desc
 09/16/11   10/1/2011        ABC12345    A      TTT222
 09/16/11   10/8/2011        ABC12345    S      BADTYPE_123
 09/16/11   11/8/2011        ABC12345    A      YYY222
 09/16/11   11/8/2011        ABC12345    A      WWW333
 09/16/11   11/8/2011        ABC12345    B      YYY222
 09/16/11   11/8/2011        ABC12345    B      WWW333   
 05/11/12   9/17/2012        ABC12345    B      ZZZ222
 05/11/12   9/17/2012        ABC12345    A      ZZZ222
 05/11/12   9/17/2012        MNO12345    B      CCC222
 05/11/12   9/17/2012        MNO12345    A      CCC222
 08/16/12   10/8/2011        MNO12345    S      BADTYPE_789
 08/16/12   10/9/2011        MNO12345    A      CCC111
 11/11/12   11/17/2012       MNO12345    S      BADTYPE_790
 12/01/12   9/17/2012        MNO12345    A      DDD222
 11/20/12   1/06/2013        XYZ98765    B      TST111
 11/20/12   1/06/2013        XYZ98765    A      TST111
 01/15/13   3/17/2013        XYZ98765    A      TST222
 05/11/13   6/15/2013        XYZ98765    B      TST111
 05/11/13   9/15/2013        XYZ98765    A      TST111

对于每个给定的match_date"group"（colA和match date，第一个"group"示例中的ABC12345和09/16/11以及ABC12345 05/11/12），如果记录类型为"S"并且包含"BADTYPE*"作为type_desc，则我希望排除/删除给定"match_dategroup"的"BADTYPE"actual_date和下一个match_dame之间的所有记录。

如果给定的"match_date组"没有"BADTYPE"记录，则应忽略该记录。如果给定match_date组的唯一记录是BADTYPE记录，那么它将保留并继续。

要删除的记录

 match_date actual_date      colA        type   type_desc
 09/16/11   11/8/2011        ABC12345    A      YYY222
 09/16/11   11/8/2011        ABC12345    A      WWW333
 09/16/11   11/8/2011        ABC12345    B      YYY222
 09/16/11   11/8/2011        ABC12345    B      WWW333   
 08/16/12   10/9/2011        MNO12345    A      CCC111

删除记录的最终结果

 match_date actual_date      colA        type   type_desc
 09/16/11   10/1/2011        ABC12345    A      TTT222
 09/16/11   10/8/2011        ABC12345    S      BADTYPE_123  
 05/11/12   9/17/2012        ABC12345    B      ZZZ222
 05/11/12   9/17/2012        ABC12345    A      ZZZ222
 05/11/12   9/17/2012        MNO12345    B      CCC222
 05/11/12   9/17/2012        MNO12345    A      CCC222
 08/16/12   10/8/2011        MNO12345    S      BADTYPE_789
 11/11/12   11/17/2012       MNO12345    S      BADTYPE_790
 12/01/12   9/17/2012        MNO12345    A      DDD222
 11/20/12   1/06/2013        XYZ98765    B      TST111
 11/20/12   1/06/2013        XYZ98765    A      TST111
 01/15/13   3/17/2013        XYZ98765    A      TST222
 05/11/13   6/15/2013        XYZ98765    B      TST111
 05/11/13   9/15/2013        XYZ98765    A      TST111

希望我已经完全说明了我要做的事情

如有任何帮助，我们将不胜感激。

Answer 1

我认为最简单、最快的方法是使用窗口函数LEAD和LAG，但就您使用SQL Server 2008而言，这是不可能的

在SQL Server 2008中，您可以使用外部应用程序-基本上，您需要的是为给定的"match_group"获取按actual_date降序排列的前一条记录，并删除前一条type_desc like 'BADTYPE%' and type = 'S'的记录

当你决定你需要什么时，你可以很容易地将该算法转录成SQL，并感受到声明性语言的力量：

delete Table1
from Table1 as T1
    cross apply
    (
        select top 1 T2.[type_desc], T2.[type]
        from Table1 as T2
        where T2.ColA = T1.ColA and T2.match_date = T1.match_date and T2.[actual_date] < T1.[actual_date]
        order by T2.[actual_date] desc
    ) as PR
where PR.[type_desc] like 'BADTYPE%' and PR.[type] = 'S'

您还可以查看SQLFIDDLE示例以了解它是如何工作的

Answer 2

我建议您对此使用递归查询
类似这样的东西：

SELECT *, 
       Row_number() 
         OVER ( 
           PARTITION BY COLA, MATCH_DATE 
           ORDER BY ACTUAL_DATE) RN 
INTO   #TEMP1 
FROM   TABLE1; 
WITH CTE 
     AS (SELECT *, 
                Cast(1 AS INT) AS flag 
         FROM   #TEMP1 
         WHERE  RN = 1 
         UNION ALL 
         SELECT t1.*, 
                CASE 
                  WHEN T2.FLAG = 2 
                        OR ( t2.TYPE_DESC LIKE 'BADTYPE%' 
                             AND t2.TYPE = 'S' ) THEN 2 
                  ELSE 1 
                END flag 
         FROM   #TEMP1 T1 
                INNER JOIN CTE T2 
                        ON T1.COLA = T2.COLA 
                           AND t1.MATCH_DATE = t2.MATCH_DATE 
                           AND t1.RN = T2.RN + 1) 
SELECT [MATCH_DATE], 
       [ACTUAL_DATE], 
       [COLA], 
       [TYPE], 
       [TYPE_DESC] 
FROM   CTE 
WHERE  FLAG = 1 
ORDER  BY COLA, 
          MATCH_DATE 

您可以在SQL Fiddle上使用一个工作示例。

祝你好运！