Hello,
I hoping somebody can help with a sql issue I have. WE have sales orders and works orders. I am looking at the works order history table called bmhstm. In this table we have works orders that have different warehouses and each record in the table has a sales order. If the works order record has a warehouse of BE, I need to see the works order, the sales order that is attached, but this is the issue, I also need to see the sales order that is attached to the sales order of the works order in the case of a BE warehouse.
Heres an example, 072216 is a works order record in the bmhstm table and in the bmwohm table it has the sales order of 072210.
But in the case of BE warehouse ONLY, 072210 also has a works order record which has its own sales order against it, in this case 051293. I need to get all three into the results of the below script. I suspect maybe a case statement in the select if the ware house is BE to use the found sales order as a works order, then go and find the sales order against it? If its another warehouse the column will be blank. Any help would be hugely appreciated.
select ROW_NUMBER() over(order by bmhstm.works_order) as Pallet,
rtrim(bmhstm.actual_warehouse) as WH,
rtrim(bmhstm.works_order) as [Works Order No],
bmhstm.quantity_finished as [Pallet Qty],
bmwohm.sales_order,
convert(varchar,event_date,103) as [Date],
rtrim(bmhstm.spare_1) as [User & time]
from scheme.bmhstm bmhstm
inner join scheme.bmwohm bmwohm
on bmhstm.actual_warehouse = bmwohm.warehouse
and bmhstm.works_order = bmwohm.works_order
where bmhstm.works_order = '072216'
order by event_date