Dataset: StoreSales

CollapseAll image

Collapse image Dataset properties


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

Collapse image Parameters


No parameters exist

Collapse image Fields


Name  Source column  Data type  Caption  Value  Is calculated field 
SalesOrderID SalesOrderID System.Int32      
TotalDue TotalDue System.Decimal      
OrderDate OrderDate System.DateTime      
CustomerID CustomerID System.Int32      
Store Store System.String      
StateProvinceCode StateProvinceCode System.String      
City City System.String      
PostalCode PostalCode System.String      
SpatialLocation SpatialLocation Microsoft.SqlServer.Types.SqlGeography      
Total: 9 field(s)

Collapse image Filters


No filters exist

Collapse image Query


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')

Collapse image See also


About this documentation