June 17, 2016 at 11:12 am
I have a list of items in different locations. They may have different suppliers but only one supplier can be marked as primary. Below, each location has two suppliers, only one is primary. I want to show only the items where supplier of 77 is not a primary for ANY location. I would hope to get only items 111-28 and 111-30 since 111-20 has 77 as primary for at least one location.
CREATE TABLE jec_item_sup (item_id varchar(10), location varchar(4), supplier varchar(4), primary_supplier varchar(1))
INSERT INTO jec_item_sup
VALUES ('111-20', 1, 77, 'Y'),
('111-20', 1, 100, 'N'),
('111-20', 2, 77, 'N'),
('111-20', 2, 100, 'Y'),
('111-28', 1, 77, 'N'),
('111-28', 1, 100, 'Y'),
('111-28', 2, 77, 'N'),
('111-28', 2, 100, 'Y'),
('111-30', 1, 77, 'N'),
('111-30', 1, 100, 'Y'),
('111-30', 2, 77, 'N'),
('111-30', 2, 100, 'Y')
I started with this, but I know it's not correct. I still see item 111-20 because location 2 is N for that supplier.
SELECT j.item_id
FROM jec_item_sup j
WHERE j.primary_supplier = 'N'
AND j.supplier = 77
GROUP BY j.item_id
June 17, 2016 at 11:28 am
Here is one way you could do it.
SELECT
j1.item_id
FROM #jec_item_sup j1
EXCEPT
SELECT j.item_id
FROM #jec_item_sup j
WHERE j.primary_supplier = 'Y'
AND j.supplier = 77;
June 17, 2016 at 12:08 pm
Fantastic. I wasn't aware of the EXCEPT feature.
June 17, 2016 at 12:23 pm
I would do it with an AND NOT EXISTS (SELECT ...) clause myself.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2016 at 1:57 pm
This works for your sample data. It may not work for your real data. It all depends upon if you can make the row that you don't want be the first row in each partition.
;with cte as
(
select item_id, location, supplier, primary_supplier,
row_number() over(partition by item_id order by primary_supplier desc) rowNum
from #jec_item_sup
)
select * from cte
where rowNum = 1
and supplier <> '77'
and primary_supplier = 'Y'
Give it a try and let us know how it goes.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 22, 2016 at 11:52 am
TheSQLGuru (6/17/2016)
I would do it with an AND NOT EXISTS (SELECT ...) clause myself.
As would I. 🙂
select distinct item_id
from jec_item_sup jis
where supplier=77
and not exists (select * from jec_item_sup where supplier=jis.supplier and item_id=jis.item_id and primary_supplier='Y')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply