/******************************************************
 *
 * Name:         outer-apply-operator.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-02-2012
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about outer apply operator.
 *
 ******************************************************/

-- Use Adventure Works
USE [AdventureWorks2012]
GO


-- Cross Apply same as inner join, drops 14 recs - (30 rows)
SELECT
      PC1.Name as MainCategory
    , PS1.Name as SubCategory
    , RGB.Name as ItemColor
    , RGB.HexCode as HexCode
    , SUM(Quantity) as SumItemStock
    , AVG(P1.ListPrice) as AvgItemList
    , AVG(P1.StandardCost) as AvgItemCost
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
    CROSS APPLY [dbo].[ufnColor2RgbValues] (P1.Color) AS RGB
WHERE
    PC1.Name IS NOT NULL
GROUP BY
    PC1.Name 
  , PS1.Name
  , RGB.Name 
  , RGB.HexCode
 

-- Out Apply same as left outer join - (44 rows)
SELECT
      PC1.Name as MainCategory
    , PS1.Name as SubCategory
    , RGB.Name as ItemColor
    , RGB.HexCode as HexCode
    , SUM(Quantity) as SumItemStock
    , AVG(P1.ListPrice) as AvgItemList
    , AVG(P1.StandardCost) as AvgItemCost
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
    OUTER APPLY [dbo].[ufnColor2RgbValues] (P1.Color) AS RGB
WHERE
    PC1.Name IS NOT NULL
GROUP BY
    PC1.Name 
  , PS1.Name
  , RGB.Name 
  , RGB.HexCode