We help IT Professionals succeed at work.

SQL Query Problem

Richard Cooper
Richard Cooper used Ask the Experts™
on
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

Open in new window


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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Just a quick question for optimization purposes, what is the maximum level of lookup from Part->ParentPart would the table contain?  The example you have is 3 levels from xxx->zzzz->aaaa
Richard CooperIT Manager

Author

Commented:
I have been told that it is a max of 5 levels, however most parts are 3 or less
I'm going out on a limb here by saying that you are querying Syspro...

I've got a query that will return the BoM for all the Part No's down any number of levels, so maybe you can use this and modify it to your needs... (Based on Syspro)

Of course you can filter on date there too if you need...

I hope this helps you get closer to the answer you are looking for.
WITH temp_BOM(Iteration,StockCode,Component,OutstQtyToMake,DateRequired) AS
(
	SELECT
		0 AS Iteration,  
		b.ParentPart, 
		b.Component, 
		m.OutstQtyToMake * b.QtyPer AS Qty, 
		m.DateRequired 
	FROM 
		BomStructure b INNER JOIN
		MrpBuildSchedule m ON m.StockCode = b.ParentPart INNER JOIN
		InvMaster i ON i.StockCode = b.Component
   WHERE m.OutstQtyToMake > 0
 UNION ALL
	SELECT 
		t.Iteration+1 AS Iteration, 
		b.ParentPart, 
		b.Component, 
		m.OutstQtyToMake * b.QtyPer AS Qty, 
		m.DateRequired 
	FROM 
		temp_BOM t INNER JOIN 
		BomStructure b ON t.Component = b.ParentPart INNER JOIN
		MrpBuildSchedule m ON m.StockCode = b.ParentPart INNER JOIN
		InvMaster i ON i.StockCode = t.Component
   WHERE m.OutstQtyToMake > 0
)


SELECT StockCode,Component,SUM(OutstQtyToMake) AS OutstQtyToMake,DateRequired from 
(SELECT DISTINCT StockCode,Component,OutstQtyToMake,DateRequired from temp_BOM) AS z
GROUP BY StockCode, Component, DateRequired
order by StockCode, DateRequired

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
So, given the data above, what would the result set look like?
Column layout and data.
Would xxx be listed anywhere or just the lowest level parts?
Richard CooperIT Manager

Author

Commented:
Skylinc,
 
 Your guess is correct however your code failed with incorrect syntax near 'Iteration'
Richard CooperIT Manager

Author

Commented:
cyberkiwi

I would like the data as follows

Top level part    topQty  lowerlevel  Qty
xxx                        1         YYYY          12
xxx                        1         aaaa            8
xxx                        1         bbbb         14
xxx                        1         WWWW      3

   
Which version of SQL are you using? Because this runs fine in SQL 2005.
Richard CooperIT Manager

Author

Commented:
SQL 2000
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
select X.StockCode [topLevelPart], X.Qty [topQty],
      Coalesce(E.Component,D.Component,C.Component,B.Component,A.Component) [lowerLevelPart],
      ISNULL(E.QtyPer,1)*ISNULL(D.QtyPer,1)*ISNULL(C.QtyPer,1)*ISNULL(B.QtyPer,1)*ISNULL(A.QtyPer,1)*X.Qty [Qty]
from (
      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
) X
inner join BOM A on A.ParentPart = X.StockCode
left join BOM B on B.ParentPart = A.Component
left join BOM C on C.ParentPart = B.Component
left join BOM D on D.ParentPart = C.Component
left join BOM E on E.ParentPart = D.Component
ORDER BY X.StockCode
Richard CooperIT Manager

Author

Commented:
Skylinc,

 Do I need a temporary table setup ?

I cannot test this in SQL 2000, and I don't know if this will work in SQL 2000, but you can try creating the temp_BOM table, and then doing an insert into it, rather that using 'WITH temp_BOM'.

However, having looked at cyberkiwi's statement, that seems to do the trick.
Just to change it so that it is Syspro compatible...

select X.StockCode [topLevelPart], X.Qty [topQty],
      Coalesce(E.Component,D.Component,C.Component,B.Component,A.Component) [lowerLevelPart],
      ISNULL(E.QtyPer,1)*ISNULL(D.QtyPer,1)*ISNULL(C.QtyPer,1)*ISNULL(B.QtyPer,1)*ISNULL(A.QtyPer,1)*X.Qty [Qty]
from (
      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, '2001-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
) X
inner join BomStructure  A on A.ParentPart = X.StockCode
left join BomStructure  B on B.ParentPart = A.Component
left join BomStructure  C on C.ParentPart = B.Component
left join BomStructure  D on D.ParentPart = C.Component
left join BomStructure  E on E.ParentPart = D.Component
ORDER BY X.StockCode

Open in new window

Richard CooperIT Manager

Author

Commented:
Is it possible to mod the SQL so it prompts for a Start Date and Finish Date?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
SQL Server does not prompt like Access...!
But you can create a table-valued function out of it.
create function dbo.GetBOM(@startDate datetime, @endDate datetime)
returns table as return
select X.StockCode [topLevelPart], X.Qty [topQty],
      Coalesce(E.Component,D.Component,C.Component,B.Component,A.Component) [lowerLevelPart],
      ISNULL(E.QtyPer,1)*ISNULL(D.QtyPer,1)*ISNULL(C.QtyPer,1)*ISNULL(B.QtyPer,1)*ISNULL(A.QtyPer,1)*X.Qty [Qty]
from (
      SELECT InvMovements.StockCode AS StockCode, SUM(InvMovements.TrnQty) AS QTY
      FROM   InvMovements
      INNER JOIN InvMaster ON InvMovements.StockCode = InvMaster.StockCode
      WHERE (InvMovements.EntryDate BETWEEN @startdate AND @enddate)
        AND (InvMovements.TrnType = 'R') AND (InvMaster.ProductClass IN ('PDM', 'PRM', 'TM', 'PM'))
      GROUP BY InvMovements.StockCode
) X
inner join BOM A on A.ParentPart = X.StockCode
left join BOM B on B.ParentPart = A.Component
left join BOM C on C.ParentPart = B.Component
left join BOM D on D.ParentPart = C.Component
left join BOM E on E.ParentPart = D.Component
ORDER BY X.StockCode
GO

=========
usage:

select * from dbo.GetBOM('20100531', '20100627')

Open in new window

Richard CooperIT Manager

Author

Commented:
Thanks,
 I was going to use the data elsewhere in excel and prompt for the date from excel
Richard CooperIT Manager

Author

Commented:
I tried the function but I get this error
Server: Msg 137, Level 15, State 2, Procedure GetBOM, Line 9
Must declare the variable '@startdate'
Richard CooperIT Manager

Author

Commented:
Fixed it changed @startdate to @startDate and @enddate to @endDate
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
"Procedure GetBOM" ? You created it as a procedure.. What does the SQL look like?
Did you try the function and the sample usage code?
Richard CooperIT Manager

Author

Commented:
I created it as function. I just copied the code.

In the start of the code you had @startDate and @endDate in the select you had @startdate and @enddate I changed the code to match.

It has created the function and it works as your example.

Thank you for your time and effort.

I will export to excel from SQL