SELECT
[StoreID] as BusinessEntityID
, s.Name as Store
-- , t.Name as AddressType -- Main Office or Shipping
, ad.PostalCode
-- , sp.CountryRegionCode
--, sp.Name as StateProvince
--, sp.StateProvinceCode
, ad.SpatialLocation.ToString() as GeoLocation
FROM [Sales].[Customer] c
INNER JOIN Sales.Store s ON s.BusinessEntityID = c.StoreID
INNER JOIN Person.BusinessEntityAddress a ON a.BusinessEntityID = s.BusinessEntityID
INNER JOIN Person.AddressType t ON a.AddressTypeID=t.AddressTypeID
INNER JOIN Person.[Address] ad ON ad.AddressID = a.AddressID
INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE -- PersonID IS NULL means Customer is a store
PersonID IS NULL
AND t.Name = N'Main Office'
AND sp.CountryRegionCode = N'US'
|