DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to analyze sales by multiple dimensions?

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

 

*/

  

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE