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)...

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...

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...

Got this sorted, thanks all for your help, much appreciated. 🙂

Thank you all very much for all the help provided. I believe I hit one final snag now using mister magoos code. The script fails with a violation primary...

This is the script I need to run:

INSERT INTO pres.scheme.fecon2

([WAREHOUSE],

[SAGE_PRODUCT_CODE] ,[PRODUCT_GROUP] ...

Hello,

I've just realised I explained this wrong, I need reference stockm as explained to find the products that have a BE and BF record. When I have that I need...

Thanks could you clarify how to do that please, I added the create table scripts but think they were removed, guess they were too long??

Thanks for reply, very useful bit Id be a bit unsure of how to put all that together.

CREATE TABLE script for fecon2:

CREATE TABLE [scheme].[fecon2](

[WAREHOUSE] [varchar](2) NOT NULL,

[SAGE_PRODUCT_CODE] [varchar](20) NOT NULL,

[PRODUCT_GROUP]...