November 8, 2013 at 9:58 am
I want to return the OrderNo from table #MatAlloc where there is no match on ItemNo and Component in #BofM AND the count of Components by ItemNo in #MatAlloc is greater than the count of Components by ItemNo in #BofM.
In the sample data below, I want to return OrderNo 33194 from #MatAlloc because it has an ItemNo Component (444) not found in #BOM AND it has more components by ItemNo (4) than does #BofM (3). The ItemNo needs to match between the two tables. Thanks for your help.
Create Table #BofM
( ItemNo Varchar(5)
, Component Varchar(5) )
Create Table #MatAlloc
( OrderNo Int
, ItemNo Varchar(5)
, Component Varchar(5) )
Insert Into #BofM
Values ('A21', '111'),
('A21', '222'),
('A21', '333')
Insert Into #MatAlloc
Values (33194, 'A21', '111'),
(33194, 'A21', '222'),
(33194, 'A21', '333'),
(33194, 'A21', '444')
Drop Table #BofM
Drop Table #MatAlloc
November 8, 2013 at 10:16 am
I haven't take my morning coffee but this should do it. Maybe someone else can come with a better solution.
SELECT OrderNo
FROM #MatAlloc
WHERE OrderNo IN(
SELECT OrderNo
FROM #MatAlloc m
WHERE NOT EXISTS( SELECT *
FROM #BofM b
WHERE m.Component = b.Component
AND m.ItemNo = b.ItemNo))
GROUP BY OrderNo
HAVING COUNT( DISTINCT Component + ItemNo) > (SELECT COUNT(*)FROM #BofM)
November 8, 2013 at 11:56 am
I tried identifing first the orders with an item not in #BofM and then did the intersect with orders matching items in #BofM but having more components. The intersect simulates the logic "AND" in this case.
-- order with an item not in #BofM
SELECT
A.OrderNo
FROM
#MatAlloc AS A
LEFT OUTER JOIN
#BofM AS B
ON B.ItemNo = A.ItemNo
AND B.Component = A.Component
WHERE
B.ItemNo IS NULL;
-- orders matching and item in #BofM but with greater number of components
SELECT
A.OrderNo
FROM
(
SELECT
OrderNo,
ItemNo,
COUNT(*) AS cnt
FROM
#MatAlloc
GROUP BY
OrderNo,
ItemNo
) AS A
INNER JOIN
(
SELECT
ItemNo,
COUNT(*) AS cnt
FROM
#BofM
GROUP BY
ItemNo
) AS B
ON B.ItemNo = A.ItemNo
AND B.cnt < A.cnt;
-- intersect of both sets
(
SELECT
A.OrderNo
FROM
#MatAlloc AS A
LEFT OUTER JOIN
#BofM AS B
ON B.ItemNo = A.ItemNo
AND B.Component = A.Component
WHERE
B.ItemNo IS NULL
)
INTERSECT
(
SELECT
A.OrderNo
FROM
(
SELECT
OrderNo,
ItemNo,
COUNT(*) AS cnt
FROM
#MatAlloc
GROUP BY
OrderNo,
ItemNo
) AS A
INNER JOIN
(
SELECT
ItemNo,
COUNT(*) AS cnt
FROM
#BofM
GROUP BY
ItemNo
) AS B
ON B.ItemNo = A.ItemNo
AND B.cnt < A.cnt
);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply