CODEWITHSUNDEEP
sqlsql-server-2000
l0ckm4
l0ckm4

Reputation: 747

sql 2000 select id where multiple row conditions are met

I am struggling to get my head around this sql.

I have a function that returns a list of items associated with a Bill of Materials BOM.

The result of the sql select 

SELECT
    BOM,
    ITEMID,
    QTY
FROM boms
WHERE
    bom='A'

is 

BOM     | ITEMID     | QTY
A       | ITEMB      | 1
A       | ITEMC      | 2

Now using that result set I am looking to query my salestable to find sales where ITEMB and ITEMC were sold in enough quantity.

The format of the salestable is as follows

SELECT
    salesid,
    itemid,
    sum(qtyordered) 'ordered'
FROM salesline
WHERE
    itemid='ITEMB'
    or itemid='ITEMC'
GROUP BY salesid, itemid

This would give me something like

salesid   | itemid   | ordered
SO-10000  | ITEMB    | 1
SO-10001  | ITEMB    | 1
SO-10001  | ITEMC    | 1
SO-10002  | ITEMB    | 1
SO-10002  | ITEMC    | 2

ideally I would like to return only SO-10002 as this is the only sale where all necessary units were sold.

Any suggestions would be appreciated. Ideally one query would be ideal but I am not sure if that is possible. Performance is not a must as this would be run once a week in the early hours of the morning.

EDIT

with the always excellent help, the code is now complete. I have wrapped it all up into a UDF which simply returns the sales for a specified BOM over a specified period of time.

Function is 

CREATE FUNCTION [dbo].[BOMSALES] (@bom varchar(20),@startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN(
select count(q.SALESID) SOLD FROM (SELECT s.SALESID
FROM
 (
   SELECT s.SALESID, ITEMID, SUM(qtyordered) AS SOLD
   FROM salesline s  inner join SALESTABLE st on st.salesid=s.SALESID
   where st.createddate>=@startdate and st.CREATEDDATE<=@enddate and st.salestype=3
   GROUP BY s.SALESID, ITEMID
 ) AS s
JOIN dbo.BOM1 AS b ON b.ITEMID = s.ITEMID AND b.QTY <= s.SOLD 
where b.BOM=@bom
GROUP BY s.SALESID
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.BOM1 WHERE BOM = @bom)) q
)

Upvotes: 0

Views: 295

Answers (3)

Ryan
Ryan

Reputation: 6866

I think this may get you the results you need. You'll have to replace @BOM with your bom:

SELECT
    DISTINCT salesid
FROM
    salesline sl
INNER JOIN boms b ON
    b.bom = @BOM
    AND b.itemid = sl.itemid
GROUP BY salesid, itemid
HAVING SUM(qtyordered) >= b.qty

From what I gather, the first query is used to get the thresholds for returning qualifying sales? Based on your example data rows I assumed that there will only be one line per salesid + itemid (basically acting as a dual field primary key) in the salesline table? If that is true I don't think there is a need to do a SUM as you have in your second example query. Let me know if I'm mistaken in any of my assumptions and I'll adjust my answer.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

This should return all sales with an exact match, i.e. same itemid and same quantity:

SELECT s.salesid 
FROM
 (
   SELECT salesid, itemid, SUM(qtyordered) AS ordered
   FROM salesline AS s 
   GROUP BY salesid, itemid
 ) AS s
JOIN 
  boms AS b
ON b.itemid = s.itemid
AND b.QTY = s.ordered
WHERE b.BOM='A'
GROUP BY s.salesid
HAVING COUNT(*) = (SELECT COUNT(*) FROM boms WHERE BOM='A');

If you want to return a sale where the quantity is greater than boms.qty youhave to change the join accordingly:

JOIN 
  boms AS b
ON b.itemid = s.itemid
AND b.QTY <= s.ordered

Untested...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do this aggregation and a having clause:

select salesid
from salesline sl
group by salesid
having sum(case when itemid = 'ITEMB' then 1 else 0 end) > 0 and
       sum(case when itemid = 'ITEMA' then 1 else 0 end) > 0;

Each condition in the having clause is counting the number of rows with each item.

Upvotes: 0

Related Questions