March 28, 2007 at 5:48 am
Hi all,
Not sure if this should be posted here but couldnt find any active suitable slot for it.
I'm trying to design a schema for a bill of materials database that will have a table of materials and a table of bills of materials(BOMs) and finally a table of the contents of each BOM.
My problem is how best to query, for a given list of materials, which BOM contain some or all of the given materials. Its routine enough to get a list of BOMs which contain at least some of the materials but this is pretty crude. I'm trying to get;
A - a list of BOMs that can be fully made using the list of materials.
B - a list of BOMs that can be partially made using the list of materials
C - some way of ranking the BOMS by the proportion of their contents that are met by the given materials...so if one is missing its a high rank...down to most missing.
Any ideas greatly appreciated. I've tried various views and subqueries but not getting very far or at the expense of performance.
Thank you
rolf
March 29, 2007 at 1:40 am
Hi rolf
take a look at the ne feature in 2005 called CTE
Good luck
wilmos
March 29, 2007 at 4:50 am
SQL alone is not quite suitable for this kind of query. Actually, it is not a real query in that sense.
What you need is a procedure that loops through the BOM (article for article) and then writes 2 counters to a table.
The first counter is raised for every for every loop-step till EOF and the second counter is raised only when a BOM article can be found in the list of articles.
So, when your BOM is made of 12 articles, then the fit is perfect for counter-values 12/12. that way, you'll have an indication between 0 and 1 about the partial fit (6/12 = 50% fit)
May 31, 2007 at 2:10 am
OK thanks for the replies..will look into the CTE on 2005..not sure if the development server will have 2005 so I need a 2000 solution as well..from initial investigation it seems that most of the CTE stuff is covered by derived and tempory tables...can anyone suggest a 2000 type solution..? I'm still not getting it, my attempts still dont produce a query where I can grab an ordered list of BOMs with a percentage value for how many materials they are missing.
Thanks
Rolf
May 31, 2007 at 2:30 am
Post what you got in terms of SQL and perhaps we can think of some suggestions. I still think running the data through a stored procedure would be the best solution..
May 31, 2007 at 4:00 am
OK in 2000 I am using three tables, one containing all materialIDs (MATERIALS), one with BOMIDs and assocaited materialIDs (BOM). The final table represents the materials available (AVAILABLEMATS), in the real scenario this table will be a temprory table, created when the user enters which materials are available, or the report injects which materials are available.
I have created two views which give me counts of the available and required materials for each BOM, thena query which returns an ordered list of BOMs with how many materials are available and how many are required.
VIEWS...
VIEW ONE REQUIREDMATERIALS
SELECT
BOM.BOMID,
Count(BOM.MaterialID) AS Required
FROM
[BOM]
GROUP BY
[BOM].BOMID;
VIEW 2 AVAILABLEMATERIALS
SELECT
BOM.RecipeID,
Count(AVAILABLEMATS.MaterialID) AS Available
FROM AVAILABLEMATS
RIGHT JOIN BOM
ON AVAILABLEMATS.MaterialID = BOM.MaterialID
GROUP BY
BOM.BOMID
QUERY
SELECT
REQUIREDMATERIALS.BOMID,
BOMDetails.BOMName, Required, available
FROM
(AVAILABLEMATERIALS
INNER JOIN REQUIREDMATERIALS
ON AVAILABLEMATERIALS.BOMID = REQUIREDMATERIALS.BOMID)
INNER JOIN BOMDetails
ON AVAILABLEMATERIALS.BOMID = BOMDetails.BOMID
Order by (([Available]-[Required] )) desc
this works in a way, its certainly a start. I still need to be able to get out the materials that are missing (obviously this will have to be a second query child-parent type) and use some sort of derived/temp table for the available materials view as this will be created dynamically.
No idea what the performance would be like using this method.
Thanks for any help
Rolf
May 31, 2007 at 2:39 pm
The issue at hand is that SQL server 2000 does not provide the EXCEPT/MINUS set operators, so you need to roll your own construction to get at the missing combinations.
One approach is to create a composite key that comprises BOM and Article.
Just a little fooling around in Jet-SQL:
SELECT BOM.BOM_ID AS BOMBOM, BOM.ARTICLE AS BOMARTICLE
FROM BOM
WHERE BOM.BOM_ID+ BOM.ARTICLE NOT IN
(SELECT BOM.BOM_ID+ARTICLE.ARTICLE FROM
BOM,ARTICLE WHERE BOM.ARTICLE = ARTICLE.ARTICLE);
Obviously you need to modify this to work in T-SQL (to create a composite key from different datatypes)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply