Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

SQL Query Problem

Avatar of Richard Cooper
Richard CooperFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
19 Comments1 Solution630 ViewsLast Modified:
I have been asked to find out how much material we have used in one month by interrogating the SQL database.

I can return all the top level parts by using the following code.
SELECT     InvMovements.StockCode AS StockCode, SUM(InvMovements.TrnQty) AS QTY
FROM         InvMovements INNER JOIN
                      InvMaster ON InvMovements.StockCode = InvMaster.StockCode
WHERE     (InvMovements.EntryDate BETWEEN CONVERT(DATETIME, '2010-05-31 00:00:00', 102) AND CONVERT(DATETIME, '2010-06-27 00:00:00', 102)) AND 
                      (InvMovements.TrnType = 'R') AND (InvMaster.ProductClass IN ('PDM', 'PRM', 'TM', 'PM'))
GROUP BY InvMovements.StockCode
ORDER BY InvMovements.StockCode

This returns the partnumber (stockcode) and the QTY manufactured for the month.
I then need to access the bom table to return the materials used for the parts.
The bom table has the following structure:

The problem is that for any given ParentPart the Component might also be made up of several parts.
ParentPart xxx is made of Component YYYY and uses QtyPer 12
ParentPart xxx is made of Component ZZZZ and uses QtyPer 2
ParentPart xxx is made of Component WWW and uses QtyPer 3

The problem I have is Component ZZZZ is made up of
ParentPart ZZZZ is made of Component aaaa and uses QtyPer 4
ParentPart ZZZZ is made of Component bbbb and uses QtyPer 7

For the example above I would need to return
ParentPart xxx  
Part YYYY Qty 12
Part aaaa Qty 8
Part bbbb Qty 14
Part WWWW Qty 3

and then multiply the individual Part QTY by the QtyPer returned in the original SQL.