/******************************************************
 *
 * Name:         cross-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 cross apply operator.
 *
 ******************************************************/

-- Use Adventure Works
USE [AdventureWorks2012]
GO


-- Break down of orders per year using group by - (4 rows)
SELECT 
    YEAR(DueDate) as 'Year'
  , COUNT(*) as 'Total'
FROM
    [Sales].[SalesOrderHeader]
GROUP BY
    YEAR(DueDate)
ORDER BY 
    YEAR(DueDate)
GO


-- Order date posts 2 days later, adjust the output - (211 rows)
SELECT 
    Adjusted.DateValue,
    COUNT(SH1.SalesOrderID) as TotalOrders,
    SUM(SH1.TotalDue) as TotalDue
FROM 
    [Sales].[SalesOrderHeader] AS SH1 INNER JOIN 
    [Sales].[SalesOrderDetail] AS SO1 ON SH1.SalesOrderID = SO1.SalesOrderID
    CROSS APPLY
    (
	SELECT CONVERT(DATE, DATEADD(DAY, -2, SH1.OrderDate))
    ) AS Adjusted (DateValue)
WHERE
    YEAR(Adjusted.DateValue) = 2008
GROUP BY
    Adjusted.DateValue
ORDER BY
    Adjusted.DateValue
GO
	

-- Use the built in TVF	for Adventure Works
SELECT * FROM [dbo].[ufnGetContactInformation] (1209)
GO


-- Get orders by country, customer, # orders, $ orders - (3582 rows)
SELECT
    ST.Name as Country,
    CI.LastName,
    CI.FirstName,
    COUNT(SH.SalesOrderID) as TotalOrders,
    SUM(SH.TotalDue) as TotalDue
FROM 
    [Sales].[SalesOrderHeader] SH 
    INNER JOIN [Sales].[Customer] AS C
	ON  SH.CustomerID = C.CustomerID
    INNER JOIN [Sales].[SalesTerritory] AS ST
	ON C.TerritoryID = ST.TerritoryID 
    CROSS APPLY [dbo].[ufnGetContactInformation] (C.PersonID) AS CI
WHERE
    C.StoreID IS NULL and ST.Name = 'Australia'
GROUP BY
    ST.Name,
    CI.LastName,
    CI.FirstName