• Status: Solved
• Priority: Medium
• Security: Public
• Views: 189

# SQL question

I have  the following tables in the attachment.  I need to find

Which parts need to be ordered from our supplier now?

Who ordered the most value?

Who ordered the most quantity?
sqltables.doc
0
ocdc
• 3
• 2
1 Solution

1) What parts need to be ordered ?  (Assuming that stockqty is your stock on hand).

You want to find all the parts where the SOH has reached the reorder point.  Your example doesn't hold a reorder quantity column so hard to say how much you're supposed to order.

``````select partid, description
from Inventory
where stockqty <= reorderpnt
``````

2) Who ordered the most value ?

This query gives you a sorted list of suppliers and the sum of the orders they had placed, in descending order of value and you take the first one.

``````select top 1 o.custid, c.cname, sum(oi.qty * i.price) as OrderValue
from Orders o
join OrderItems oi on o.orderid = oi.orderid
join Inventory i on oi.partid = i.partid
join Customers c on o.custid = c.custid
group by o.custid, c.cname
order by OrderValue desc
``````

3) Who ordered the most quantity

We can take the previous query and change it up just a little.

``````select top 1 o.custid, c.cname, sum(oi.qty) as OrderQty
from Orders o
join OrderItems oi on o.orderid = oi.orderid
join Customers c on o.custid = c.custid
group by o.custid, c.cname
order by OrderQty desc
``````

I'm sure there are other ways to do it, but I believe this comes pretty close to it (I have a test system with close to this layout of data, so I checked the basis of these queries there and it seemed to give me the correct data - and then converted the column and table names to match your specification).

Also won't promise that it's the most efficient way of doing things, I don't do a lot of development these days - but it seems to get the job done :)
0

Author Commented:

Which sales people have NOT sold anything?    Subquery version

Which sales people have NOT sold anything? JOIN version (explicit/named JOIN)
0

Assuming you have an Employees table (that's not in your attachment):

Salesmen with no sales (Subquery)

``````select empid
from Employees
where empid not in (select empid from Orders);
``````

Salesmen with no sales (JOIN)

``````select empid
from
(
select e.empid, max(o.salesdate) as last_sale
from employees e
left outer join orders o
on e.empid = o.empid
group by e.empid
)
where last_sale is null;
``````

Alternatively, you could use distinct:

``````select distinct empid
from
(
select e.empid, o.salesdate as last_sale
from employees e
left outer join orders o
on e.empid = o.empid
)
where last_sale is null;
``````
0

Author Commented:
Thanks.   final one is

Who placed the most orders?
0

select top 1 custid, count(*) as num_orders
from Orders
group by custid
order by num_orders desc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.