Dataset: StoreSaleTotals

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 
StateProvinceCode StateProvinceCode System.String      
NStores NStores System.Int32      
Total Total System.Decimal      
Total: 3 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT sp.StateProvinceCode, COUNT(DISTINCT (c.StoreID)) as NStores, SUM(soh.TotalDue) AS Total
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')
GROUP BY sp.StateProvinceCode
ORDER BY sp.StateProvinceCode

Collapse image See also


About this documentation