###### 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

Deciding to stick with EE.

Mohamed Asif

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

Carl Webster
CTP, Sr Infrastructure Consultant

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

Troubleshooting
Research
Professional Opinions
###### 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

Microsoft SQL ServerSQL
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:
ParentPart
Component
QtyPer

The problem is that for any given ParentPart the Component might also be made up of several parts.
e.g.
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
uses
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.