• Well with a little tinkering here is what I came up with:

    ;with C_Orders_CTE (conum, itemnum, coitemstat) as (

    select conum

    , itemnum

    , coitemstat

    from @Orders

    where coitemstat = 'C'

    ), O_Orders_CTE (conum, itemnum, coitemstat) as (

    select conum

    , itemnum

    , coitemstat

    from @Orders

    where coitemstat = 'O'

    )

    select distinct ord.conum

    from @Orders ORD inner join

    C_Orders_CTE C_CTE on ord.conum = C_CTE.conum

    and ord.itemnum = C_CTE.itemnum left join

    O_Orders_CTE O_CTE on C_CTE.conum = O_CTE.conum

    where O_CTE.conum is null

    Here is the result I get:

    conum

    AW00000253

    SF00000389

    SF00000390

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman