I did create one in the past, and it worked on a “small” database (the training db) but when you put it into a real world database, it would timeout.
The biggest problem is:
“How do you determine the ‘current revision’”… it is not as easy as you might think.
- it is DATE based (effective date)
- it is based on the current SITE, and whether there is an alternate method.
- the “Approved” flag must also be examined.
There can only be one approved/effective revision that will be used in your query, but if you have 10 approved revisions, each with a BOM, “Which one is effective”… does it have a future effectivity date? then it is not the current one… etc etc.
This seemed to be the significant stumbling block in the query… but I got it working…
if there is only ONE APPROVED revision for each part, then this stumbling block goes away.