****************************************************************************************************
Location : Microsoft Reporting Services
Host     : bnbdatabase1
Path     : Sales_by_Region_2008R2
****************************************************************************************************

-------------------------------------------------- Datasets --------------------------------------------------

-- Query(s)

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')
-- Query(s)

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')
-- Query(s)

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