SQL query to exclude records based on type and dates

ORIGINAL

 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

For each given match_date "group" (colA and Match date, ABC12345 and 09/16/11 and ABC12345 05/11/12 in first "group" example), if a record is type "S" and contains "BADTYPE*" as the type_desc, I want to exclude/delete all records between the "BADTYPE" actual_date and the next match_date for a given "match_date group".

If there are no "BADTYPE" records for a given "match_date group", it should be ignored. If the only record for a given match_date group is the BADTYPE record, then it will remain and continue.

RECORDS TO BE DELETED

 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

FINAL RESULTS WITH RECORDS REMOVED

 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

Hopefully I've fully illustrated exactly what I'm trying to do.

Any help would be greatly appreciated.

Answers


I think the easiest and fastest way to do it is to use windowed functions LEAD and LAG, but as far as you are using SQL Server 2008, it's not possible for you.In SQL Server 2008, you could use outer apply - basically what you need is to get previous record for given "match_group" ordered by actual_date descending and delete record if previous type_desc like 'BADTYPE%' and type = 'S' When you decide what you need, you could pretty easily transcribe that algorithm into SQL, and feel the power of declarative languages:

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'

you could also check SQL FIDDLE EXAMPLE to see how it's working


I suggest you use a recursive query for this. Something like this:

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 

You can play around with a working example on SQL Fiddle.

Good luck!


Need Your Help

AJAX Requests using 1GB of local RAM on Machine

javascript jquery ajax

I have a website that uses a lot of AJAX requests, almost 1 every 2-3 seconds.

What is Bison and why is it useful?

c++ c bison

I have been programming for a few years now and have seen the name Bison in passing, but never bothered to ask why it is or why it might be needed. How can Bison effect how I program, can it make my

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.