/*****************************************************************************
*   Presentation: Module 13 - Extended Events 
*   FileName:  Demo 1a - SQLskills_RecompilingProcedures_Setup.sql
*
*   Summary: Setup Objects for Module 13 Demo1
*   Summary: Creates the AnnualTop5SalesPersonByMonthlyPercent stored
*			 procedure in the AdventureWorks2008 database.
*
*   Date: March 14, 2011 
*
*   SQL Server Versions:
*         2008, 2008 R2
*         
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved. 
*
*   For more scripts and sample code, check out 
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give 
*	due credit. 
*
*
*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
*   PARTICULAR PURPOSE. 
*
******************************************************************************/

USE [AdventureWorks2008R2]
GO
IF OBJECT_ID('AnnualTop5SalesPersonByMonthlyPercent') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[AnnualTop5SalesPersonByMonthlyPercent]
END
GO

CREATE PROCEDURE [dbo].[AnnualTop5SalesPersonByMonthlyPercent] (@Year int)
AS
BEGIN
	CREATE TABLE tempdb.dbo.MonthlyTotals (MonthNumber int primary key, TotalSales decimal(18,2))
	
	INSERT INTO tempdb.dbo.MonthlyTotals (MonthNumber, TotalSales)
	SELECT (DATEPART(YEAR, OrderDate)*100)+DATEPART(MONTH, OrderDate), SUM(TotalDue)
	FROM Sales.SalesOrderHeader
	WHERE SalesPersonID IS NOT NULL
		AND DATEPART(YEAR, OrderDate) = @Year
	GROUP BY (DATEPART(YEAR, OrderDate)*100)+DATEPART(MONTH, OrderDate)
	
	CREATE TABLE tempdb.dbo.EmpSalesPercentByMonth
	(SalesPersonID int, MonthNumber int, TotalSales decimal(18,2), PercentMonthTotal decimal(18,2))
	
	INSERT INTO tempdb.dbo.EmpSalesPercentByMonth (SalesPersonID, MonthNumber, TotalSales)
	SELECT SalesPersonID, (DATEPART(YEAR, OrderDate)*100)+DATEPART(MONTH, OrderDate), SUM(TotalDue)
	FROM Sales.SalesOrderHeader
	WHERE SalesPersonID IS NOT NULL
		AND DATEPART(YEAR, OrderDate) = @Year	
	GROUP BY SalesPersonID, (DATEPART(YEAR, OrderDate)*100)+DATEPART(MONTH, OrderDate)

	CREATE CLUSTERED INDEX IX_EmpSalesPercentByMonth_MonthNumber ON tempdb.dbo.EmpSalesPercentByMonth (MonthNumber)

	UPDATE tempdb.dbo.EmpSalesPercentByMonth
	SET PercentMonthTotal = tempdb.dbo.EmpSalesPercentByMonth.TotalSales/tempdb.dbo.MonthlyTotals.TotalSales
	FROM tempdb.dbo.EmpSalesPercentByMonth
	JOIN tempdb.dbo.MonthlyTotals ON tempdb.dbo.EmpSalesPercentByMonth.MonthNumber = tempdb.dbo.MonthlyTotals.MonthNumber
	
	DROP TABLE tempdb.dbo.MonthlyTotals
	
	SELECT TOP 5 * 
	FROM tempdb.dbo.EmpSalesPercentByMonth
	ORDER BY PercentMonthTotal DESC
	
	DROP TABLE tempdb.dbo.EmpSalesPercentByMonth
END;

GO

