July 4, 2016 at 4:43 am
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
July 4, 2016 at 4:55 am
July 4, 2016 at 5:01 am
dandrews 45259 (7/4/2016)
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
Are you saying that the sales order 072210 in the bmwohm table has TWO related rows in the bmhstm table, with works orders of 072216 and 072210? And the works order 072210 has a sales order in bmwohm?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 5:04 am
Can you post the desired sample output.
July 4, 2016 at 5:08 am
PalletWHWorks Order NoPallet Qtysales_orderDateUser & time
1BE0722163970072210 26/10/2015jburnett at 08:12
it would be as the above in the case of works order 072216, but there would be an additional column called '2nd sales order' ad in this case it would be 051293
July 4, 2016 at 5:15 am
No Chris, if the Warehouse is BE for a works order record, its sales order is also a works order with its own related sales order.
072216 is a works order with 072210 as its sales order
but 072210 is also a works order with a sales order related to it > 051293
July 4, 2016 at 5:28 am
July 4, 2016 at 5:34 am
dandrews 45259 (7/4/2016)
No Chris, if the Warehouse is BE for a works order record, its sales order is also a works order with its own related sales order.072216 is a works order with 072210 as its sales order
but 072210 is also a works order with a sales order related to it > 051293
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,
bmwohm2.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
LEFT JOIN scheme.bmwohm2
on bmwohm2.warehouse = 'BE'
AND bmwohm2.works_order = bmwohm.sales_order
where bmhstm.works_order = '072216'
order by event_date
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 5:54 am
thanks so much for your help Chris, much appreciated, that put me in the right direction, I made a few tweaks and now its working perfectly:
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 as [Sales Order],
isnull(bmwohm2.sales_order, 'N/A') as [2nd BE 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
left outer JOIN scheme.bmwohm bmwohm2
on bmwohm.warehouse = 'BE'
AND bmwohm2.works_order = bmwohm.sales_order
where bmhstm.works_order = @works_order
order by event_date
thanks so much all.
July 4, 2016 at 6:04 am
Well done dandrews, and many thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply