|
Execute the following
Microsoft SQL Server T-SQL database script in SSMS Query Editor to demonstrate the preparation of a multi-dimensional sales analysis report applying a CTE - Common Table Expression - with 7 INNER JOINs. The following dimensions are used in the analysis: GeoRegion, Country, SalesPerson, Category, SubCategory, Product, Color, Size.
-- SQL cte - SQL inner join - SQL multidimensional report - SQL multi-table queries
USE AdventureWorks2008;
WITH cteSalesDetail AS (
SELECT
pp.LastName+', '+pp.FirstName AS SalesPerson,
convert(date,soh.OrderDate) AS [OrderDate],
SUM(sod.OrderQty) AS Qty,
SUM(sod.LineTotal) AS LineTotal,
st.[Group] AS [GeoRegion],
st.Name AS Country,
pc.Name AS Category,
psc.Name AS SubCategory,
prod.Name AS Product,
COALESCE(prod.Color,'') AS Color,
COALESCE(prod.Size, '') AS Size,
soh.SalesOrderNumber AS [OrderNumber]
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh
ON sp.BusinessEntityID = soh.SalesPersonID -- Primary Key - Foreign Key JOIN
INNER JOIN Person.Person AS pp
ON pp.BusinessEntityID = sp.BusinessEntityID -- PK-FK JOIN
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product AS prod
ON sod.ProductID = prod.ProductID
INNER JOIN Sales.SalesTerritory AS st
ON st.TerritoryID = sp.TerritoryID
INNER JOIN Production.ProductSubcategory AS psc
ON prod.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS pc
ON pc.ProductCategoryID = psc.ProductCategoryID
GROUP BY pc.Name, soh.OrderDate, soh.SalesOrderNumber,
psc.Name, prod.Name,
pp.LastName+', '+pp.FirstName,
st.CountryRegionCode, st.Name,st.[Group],
prod.Color, prod.Size
HAVING st.[Group] != 'North America' )
SELECT
GeoRegion, Country, SalesPerson, Category, SubCategory, Product,
Color, Size,
QtyTotal=SUM(Qty),
DollarTotal='$'+convert(varchar, convert(money,SUM(LineTotal)),1)
FROM cteSalesDetail
GROUP BY GeoRegion, Country, SalesPerson, Category, SubCategory,
Product, Color, Size
ORDER BY GeoRegion, Country, Category, SubCategory, Product,
Color, Size
GO
/* Partial results
GeoRegion Country Category SubCategory Product
Europe Germany Bikes Road Bikes Road-750 Black, 52
Europe Germany Bikes Road Bikes Road-750 Black, 58
Europe Germany Bikes Touring Bikes Touring-1000 Blue, 46
Europe Germany Bikes Touring Bikes Touring-1000 Blue, 50
Europe Germany Bikes Touring Bikes Touring-1000 Blue, 54
*/
|
