T SQL Orders & BOMS

Question

Good Morning All,

So I have two tables e.g. order_details and bom_details

The tables are stored in a T SQL database on a 2008 Microsoft SQL Server

order_details - Containing Orders - Example:

+----------+-----------+-----+----------+
| order_no | warehouse | QTY | product  |
+----------+-----------+-----+----------+
|   080808 |        03 |  10 | 85080-00 |
+----------+-----------+-----+----------+

bom_details - Containing BOM Information - Example:

+----------+--------+-------+-------+-------------------+---------------------+
|top_product| top_wh | usage | level | component_product | component_warehouse |
+----------+--------+-------+-------+-------------------+---------------------+
| 85080-00  |     03 |    15 |    01 | 10000-00          |                  01 |
| 85080-00  |     03 |    12 |    02 | 10000-01          |                  01 |
| 85080-00  |     03 |    16 |    03 | 10000-02          |                  01 |
+----------+--------+-------+-------+-------------------+---------------------+

I would like to be able to input an order number for example:

080808

And it would tell me what I need e.g.

+-------------+--------+--------------+-------------------+--------------+--------------+
| top_product | top_wh | qty_required | component_product | component_wh | qty_required |
+-------------+--------+--------------+-------------------+--------------+--------------+
| 85080-00    |     03 |           10 | 10000-00          |           01 |          150 |
| 85080-00    |     03 |           10 | 10000-01          |           01 |          120 |
| 85080-00    |     03 |           10 | 10000-02          |           01 |          160 |
+-------------+--------+--------------+-------------------+--------------+--------------+

Our purchasing department are currently achieving this through some really nasty vlookup's, that take an age to load and crash excel the majority of the time!

I would like to be able to achieve this at SQL level so I can get some reports written and automate the whole process.

What would be the best way about going at this?

I do also have access to Crystal Report Designer 2008 but I do not know how to achieve this through this front end.

For anybody that's interested we are using SAGE 1000 ERP and our support company has said the only way to achieve this through the front end is to enable MRP which still does not achieve exactly what I am asking.

Answer 1

try this query,

SELECT 
    bd.top_product, 
    bd.top_wh, 
    od.QTY, bd.component_product, 
    bd.component_warehouse, 
    bd.Usage * od.Qty AS qty_required 
FROM order_details od
INNER JOIN bom_details bd ON bd.top_product = od.product