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