Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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
Asked:
ocdc
  • 3
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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

Open in new window


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

Open in new window


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

Open in new window


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
 
ocdcAuthor Commented:
Thanks. they worked.    what about  

Which sales people have NOT sold anything?    Subquery version

Which sales people have NOT sold anything? JOIN version (explicit/named JOIN)
0
 
Steve WalesSenior Database AdministratorCommented:
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);

Open in new window


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;

Open in new window


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;

Open in new window

0
 
ocdcAuthor Commented:
Thanks.   final one is  

 Who placed the most orders?
0
 
Steve WalesSenior Database AdministratorCommented:
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now