/******************************************************
 *
 * Name:         group-by-with-cube.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-21-2012
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about group by with cube.
 *
 ******************************************************/

-- Use Adventure Works
USE [AdventureWorks2012_CS]
GO


-- Delete existing view
IF  EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[Production].[vw_Inventory_Cube1]') 
    AND OBJECTPROPERTY(object_id, N'IsView') = 1)
DROP VIEW [Production].[vw_Inventory_Cube1]
GO


-- Create a view for the inventory cube
CREATE VIEW [Production].[vw_Inventory_Cube1]
AS
  SELECT
      -- Main
      CASE
	WHEN (GROUPING(PC1.Name) = 1) THEN 'All'
        ELSE ISNULL(PC1.Name, 'Unknown')
      END AS MainCategory,

      -- Sub
      CASE
	WHEN (GROUPING(PS1.Name) = 1) THEN 'All'
        ELSE ISNULL(PS1.Name, 'Unknown')
      END AS SubCategory,

      -- Color
      CASE
	WHEN (GROUPING(P1.Color) = 1) THEN 'All'
        ELSE ISNULL(P1.Color, 'Unknown')
      END AS ItemColor,

      -- Statistics
      SUM(Quantity) as ItemStock
    , FORMAT(AVG(P1.ListPrice), 'C', 'en-us') as AvgList
    , FORMAT(AVG(P1.StandardCost), 'C', 'en-us') as AvgCost

  FROM
      [Production].[ProductInventory] AS PI1
      LEFT JOIN [Production].[Product] AS P1
         ON PI1.ProductID = P1.ProductID
      LEFT JOIN [Production].[ProductSubcategory] AS PS1
	 ON P1.ProductSubcategoryID = PS1.ProductSubcategoryID
      LEFT JOIN [Production].[ProductCategory] as PC1
	 ON PS1.ProductCategoryID = PC1.ProductCategoryID
  WHERE
      PC1.Name IS NOT NULL
  GROUP BY
      PC1.Name
    , PS1.Name
    , P1.Color
  WITH CUBE
GO


-- Show all data
SELECT * FROM [Production].[vw_Inventory_Cube1]


-- Does bike color matter?
SELECT 
    SubCategory
    , ItemColor
    , ItemStock
    , AvgList 
FROM [Production].[vw_Inventory_Cube1]
WHERE MainCategory = 'All' and SubCategory like '%Bikes%'
ORDER BY SubCategory, ItemStock, ItemColor


-- What is the stock level by category
SELECT 
    MainCategory
    , ItemStock
    , AvgList
    , AvgCost
FROM [Production].[vw_Inventory_Cube1]
WHERE MainCategory <> 'All' and SubCategory = 'All' and ItemColor = 'All'
ORDER BY MainCategory, ItemStock