select records with a column with specific value on specific date sql server 2000

Question

I have the following table:

    ColA    ColB    ColC
    1       A       08/22/2013
    2       B       08/22/2013
    3       A       08/28/2013
    4       C       08/28/2013

How to select all records but the ones with ColB='A' with a date older than today's date 08/28/2013

Result should be like below:

    ColA    ColB    ColC        
    2       B       08/22/2013
    3       A       08/28/2013
    4       C       08/28/2013

Answer 1

You should be able to use a WHERE filter with NOT IN to get the result:

select cola, colb, colc
from yourtable
where cola not in (select cola
                   from yourtable
                   where colb = 'A'
                     and colc < '2013-08-28');

See SQL Fiddle with Demo