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