That's indeed what's required, the sites are also 'unique' per customer', but a site can have a different name.

Basically this is for an order delivery application for delivery of building materials to construction sites, on an order multiple sites can be present but each site belongs to 1 order and 1 customer.

The new application is in place so this is to pull the data over from the old system, the old system did not have a table to store sites since they were stored in the order delivery detail table which also contains what was being delivered. The old system didn't check whether the site is present or not. SO the Name of the site could differ as could the postcode and contact, although the latter really isn't that important in the new system.

This problem started affecting the link table between orders and sites where wrong sites got linked.

so I came up with the following

I use a row number to get the best postcode RNPC, and a row number for the best name RNC

WITH CTE

(

[Sell-to Customer No_],[Name],[Name 2],[Address],[Address 2],[City],[Contact],[Country Code],

[Phone No_],[GSM],[Fax No_],[PrintOnInvoice],[Post Code],[Global Dimension 1 Code],[Global Dimension 2 Code],

[NoSeries],[Territory Code],[Contact No_],[Creation Date],[Tender Date],[Contact No_ 1],[Business Relation 1],

[Contact No_ 2],[Business Relation 2],[Contact No_ 3],[Business Relation 3],[30bis Identification],[Comments],

RNPC,RNC) AS

(

select distinct

oh.Customer,ISNULL(LEFT(od.Name1,50),''),ISNULL(LEFT(od.Name2,50),''),ISNULL(UPPER(LEFT(od.Street1,50)),''),

ISNULL(LEFT(od.Street2,50),''),ISNULL(UPPER(LEFT(od.Place,30)),''),ISNULL(LEFT(od.Name1,30),'')

,ISNULL(UPPER(LEFT(od.Country,10)),''),ISNULL(LEFT(od.Telephone,30),''),'','',1,

ISNULL(LEFT(od.Postcode,20),''),'','','','','','1753-01-01 00:00:00.000','1753-01-01 00:00:00.000','','','','','','','',0,

ROW_NUMBER() OVER(PARTITION BY oh.Customer,od.Street1,od.Place,od.Country ORDER BY od.Postcode) As RNPC,

ROW_NUMBER() OVER(PARTITION BY oh.Customer,od.Street1,od.Place,od.Country ORDER BY od.Name1) As RNC

from VMSER_TP7VM.dbo.Orderdelivery od

inner join VMSER_TP7VM.dbo.Orderheader oh on od.OrderId = oh.OrderID

where 1 = 1

and od.Street1 IS NOT NULL

)

select [Sell-to Customer No_],Name,[Address],City,[Country Code],

ISNULL(CASE WHEN CTE.[Post Code] = ''

THEN (select top 1 [Post Code] from CTE t

where 1 = 1

and t.City = CTE.City

and CTE.City <> ''

and t.[Post Code] <> '')

ELSE CTE.[Post Code]

END,'')

[Post Code],

RNPC,RNC

from CTE

where 1 =1

--and RNPC = 1

and RNC > 1

--and Name = 'WERF BOSTOEN'

--and CTE.Address = 'DEPOT'

order by city,address

This also showcases why I use where 1 = 1, if anyone thinks this can be better please tell.