Skip to content
Advertisement

Exclude Records Based on Criteria

I need to exclude the record:

  1. If a customer bought “Shoe” and “Hat” on the same day, exclude the record with “Hat”.
  2. If a customer bought “Shoe” and “Cloth” on the same day, exclude the record with “Cloth”.

Here is the sample data:

create table tbl
(
  Customer  varchar(5),
  Day date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      'Hat',          10.00),
('B',       '5/8/2018',      'Shoe',         10.00),
('C',       '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',      'Cloth',       10.00),
('E',       '7/2/2020',      'Hat',        10.00),
('A',       '7/5/2022',      'Hat',       10.00),
('C',       '6/6/2020',      'Shoe',        10.00),
('A',       '1/1/2019',      'Cloth',        10.00),
('E',       '7/2/2020',      'Shoe',       10.00);

select *
from   tbl
group by Customer, Day, Product, Cost
order by Customer, Day

Here are the expected results:

enter image description here

Advertisement

Answer

Self join to your shoe criteria and make sure it is not there, like this:

select tbl.Customer, tbl.Day, tbl.Product, tbl.Cost
from   tbl
left join tbl as shoe on tbl.customer = shoe.customer and
                    tbl.day = shoe.day and
                    shoe.product = 'Shoe'
where not (tbl.product in ('Hat','Cloth') and coalesce(shoe.product,'') = 'Shoe')
group by tbl.Customer, tbl.Day, tbl.Product, tbl.Cost
order by tbl.Customer, tbl.Day
Advertisement