SELECT soh.SalesOrderID, soh.TotalDue, soh.OrderDate,
c.CustomerID, p.FirstName, p.LastName,
RTRIM(sp.StateProvinceCode) as StateProvinceCode,
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.PersonID INNER JOIN
Person.Person AS p ON p.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.PersonID IS NOT NULL)
AND (at.Name = N'Home')
AND (sp.CountryRegionCode = N'US') |