Dataset: CustomerLocations

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Data source name   AdventureWorks 
 Referenced data set    
 Command type   Query 

Collapse image Parameters


Name 
@GeoLocation
@Radius
Total: 2 parameter(s)

Collapse image Fields


Name  Source column  Data type  Caption  Value  Is calculated field 
CustomerID CustomerID System.Int32      
BusinessEntityID BusinessEntityID System.Int32      
Name1 Name1 System.String      
Name2 Name2 System.String      
EmailAddress EmailAddress System.String      
City City System.String      
PostalCode PostalCode System.String      
StateProvince StateProvince System.String      
StateProvinceCode StateProvinceCode System.String      
DistanceinMiles DistanceinMiles System.Double      
SpatialLocation SpatialLocation Microsoft.SqlServer.Types.SqlGeography      
NumberCarsOwned NumberCarsOwned System.Int32      
CommuteDistance CommuteDistance System.String      
Total: 13 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT c.CustomerID, c.PersonID AS BusinessEntityID, p.FirstName AS Name1, p.LastName AS Name2, ea.EmailAddress, ad.City, ad.PostalCode, 
                  sp.Name AS StateProvince, sp.StateProvinceCode, ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 AS DistanceinMiles, ad.SpatialLocation, 
                  p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:NumberCarsOwned) [1]',
                   'int') AS NumberCarsOwned, 
                  p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:CommuteDistance) [1]',
                   'varchar(30)') AS CommuteDistance
FROM     Sales.Customer AS c INNER JOIN
                  Person.Person AS p ON p.BusinessEntityID = c.PersonID INNER JOIN
                  Person.BusinessEntityAddress AS a ON a.BusinessEntityID = p.BusinessEntityID INNER JOIN
                  Person.AddressType AS t ON a.AddressTypeID = t.AddressTypeID INNER JOIN
                  Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
                  Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID INNER JOIN
                  Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE  (c.StoreID IS NULL) AND (t.Name = N'Home') AND (sp.CountryRegionCode = N'US') AND (ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 < @Radius)

Collapse image See also


About this documentation