Return results by group SQL Server

Question

The results need to only return shipmentlookupcode with status cancelled if a shipmentlookupcode has a cancelled and completed or released then do not show in the results.

query:

select      
            tv.ShipmentLookupCode,
            ts.name as taskStatuses

from        dbo.TasksView tv
join        dbo.TaskStatuses ts on tv.statusId = ts.id

where       tv.ShipmentLookupCode in('DS728352','DS727731','DS729480','DS730202','DS730222')
and         tv.operationCodeId=8

order by    tv.ShipmentLookupCode

Query results:

ShipmentLookupCode  taskStatuses
DS727731    Completed
DS727731    Completed
DS727731    Cancelled
DS727731    Completed
DS728352    Completed
DS728352    Cancelled
DS729480    Completed
DS729480    Cancelled
DS729480    Completed
DS729480    Cancelled
DS729480    Completed
DS729480    Cancelled
DS730202    Cancelled
DS730222    Cancelled
DS730222    Cancelled

So, based on the results the query should only return the following everything else no:

DS730202    Cancelled
DS730222    Cancelled
DS730222    Cancelled

Answer 1

You can make a CTE of records that only have cancelled status, then use that list to filter your original table:

create temp table testtable (ShipmentLookupCode varchar(100), taskStatuses varchar(100));

insert into testtable (ShipmentLookupCode, taskStatuses)
values    
('DS727731',    'Completed'),
('DS727731',    'Completed'),
('DS727731',    'Cancelled'),
('DS727731',    'Completed'),
('DS728352',    'Completed'),
('DS728352',    'Cancelled'),
('DS729480',    'Completed'),
('DS729480',    'Cancelled'),
('DS729480',    'Completed'),
('DS729480',    'Cancelled'),
('DS729480',    'Completed'),
('DS729480',    'Cancelled'),
('DS730202',    'Cancelled'),
('DS730222',    'Cancelled'),
('DS730222',    'Cancelled');
  
with cte as (
    select
        ShipmentLookupCode
        -- , sum(case when taskStatuses = 'Cancelled' then 0 else 1 end)
    from testtable
    group by ShipmentLookupCode
    having sum(case when taskStatuses = 'Cancelled' then 0 else 1 end) = 0    
)    
select *
from testtable
where ShipmentLookupCode in (
    select
        ShipmentLookupCode
    from cte
);