SELECT soh.SalesOrderID, soh.TotalDue, soh.OrderDate, c.CustomerID,
st.Name AS Store,
sp.StateProvinceCode,
-- StateProvinceCode is 3 chars.
-- Use RTRIM here or in the expression for the match field.
ad.City, ad.PostalCode, ad.SpatialLocation
FROM Sales.SalesOrderHeader AS soh INNER JOIN
Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
Person.BusinessEntity AS b ON b.BusinessEntityID = c.StoreID INNER JOIN
Sales.Store AS st ON st.BusinessEntityID
= b.BusinessEntityID INNER JOIN
Person.BusinessEntityAddress AS a
ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID
INNER JOIN Person.StateProvince AS sp
ON sp.StateProvinceID = ad.StateProvinceID
WHERE (c.StoreID IS NOT NULL)
AND (at.Name = N'Main Office')
AND (sp.CountryRegionCode = N'US') |