Solved

Need to optimize stored proc- takes 8 hours to run

Posted on 2007-11-29
2
137 Views
Last Modified: 2010-03-19
Ok....The following procedure takes over 8 hours to run.  I know a part of the problem is my clients hardware configuration, but there are over 30 function calls in this procedures which is doing a lot of calculations in a temp table and then populating a reporting table.  I have attempted nested stored procedures but the processing time was not cut down by much.  I can remove all of the functions but other procedures require them and I am attempting to keep the code uniform and easy to manage.  Any suggestions would be help.

tcgeeks
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
-- DROP TABLE #OUTPUT_DAILY_AOM_MB1127
 
 
 
CREATE PROCEDURE 
 
dbo.usp_TOSH_DAILY_AOM_2007_MPB1127
 
as
 
Begin Transaction
 
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
 
 
/*
-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
-- USED to Turn On/Off Required Code
 	SET @Run_Required_Code_Only = 'Y' to run ONLY the Required Code
 	SET @Run_Required_Code_Only = 'N' to run ALL of the Code
-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
*/
 
	DECLARE @Run_Required_Code_Only VarChar(1)
	SET @Run_Required_Code_Only = 'Y'
 
-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
 
 
 
/*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
PART I - CREATE a Temporary Table w/ the list of Sales Reps 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
*/
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
	-- STEP 1:  DECLARE and INITIALIZE Variables
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
	-- a. DECLARE Date Variables
		DECLARE @SOM DateTime
		DECLARE @EOM DateTime
		DECLARE @TODAY DateTime
		DECLARE @SOM_ReActivation DateTime
	
	-- b. INITIALIZE Date Variables
		-- GET the Current Date
		SET @TODAY = Toshiba_Mkt.dbo.fn_DateOnly(GetDate())
 
		-- If the Current Date = the 1st day of a Month (i.e. 04/01/2005) 
		-- then calculate the previous Month's SOM & EOM (i.e. SOM = 03/01/2005 & EOM = 03/31/2005) 
			IF (Day(@TODAY) = 1)
			BEGIN
				SET @SOM = Cast((Cast(Month((DateAdd(mm, -1, @TODAY))) as VarChar(2)) + '/01/' + Cast(Year((DateAdd(mm, -1, @TODAY))) as VarChar(4))) as DateTime)
				SET @EOM = (DateAdd(mm, 1, @SOM)-1)
			END
		-- If the Current Date <> the 1st day of a Month (i.e. 04/02/2005)
		-- then calculate the current Month's SOM & EOM (i.e. SOM = 04/01/2005 & EOM = 04/30/2005) 
			IF (Day(@TODAY) <> 1)
			BEGIN
				SET @SOM = Cast((Cast(Month(@Today) as VarChar(2)) + '/01/' + Cast(Year(@Today) as VarChar(4))) as DateTime)
				SET @EOM = (DateAdd(mm, 1, @SOM)-1)
			END
 
		-- Start Of Month for ReActivation Customers
		SET @SOM_ReActivation = DateAdd(mm, -11, @SOM)
 
 
	-- c. REMOVE Records for current day if records exist in the LOG table
		DELETE FROM TOSH_DAILY_AOM_2007_MPBTestTable
		WHERE Record_Date = @TODAY
 
 
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
	-- STEP 2:  GET LIST of Managers & Sales Reps - Using MAX Sales Rep Table & joining the ToshibaNet EmployeeActive Table for the addition info stored in TblEmployeeActive
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
	-- a. GET List of SALES REPS and their MANAGERS, CREATE empty fields for Final OutPut Table
		SELECT
		#MANAGERS.MgrUserCode,
		#MANAGERS.ManagerFirstName,
		#MANAGERS.ManagerLastName,
		SLS.Sales_Rep_Code,
		SLS.Sales_Rep_Name,
		#MANAGERS.SalesNumber,
		#MANAGERS.ACDID,
		#MANAGERS.UserCode,
 
		#MANAGERS.EmployeeFirstName,
		#MANAGERS.EmployeeLastName,
		#MANAGERS.Division,
		SLS.Division_Number,
		Cast(0 as Numeric(18)) as STATS_Ttl_Accounts,			-- Total Number of Accounts owned "Point-In-Time" by Sales Rep
		Cast(0 as Numeric(18)) as STATS_Ttl_Contacts,			-- Total Number of Account Contacts "Point-In-Time" by Sales Rep's owned Accounts
		Cast(0 as Numeric(18)) as STATS_Ttl_Business,			-- Subset of "STATS_Ttl_Accounts" - Business Segments (01,02,03,04,05) respectively (MIB,SMB,MEB,LGB,UNK) 
		Cast(0 as Numeric(18)) as STATS_Ttl_Reseller,			-- Subset of "STATS_Ttl_Accounts" - Reseller Segments (06)(RES) 
		Cast(0 as Numeric(18)) as STATS_Ttl_Government,			-- Subset of "STATS_Ttl_Accounts" - Governemnt Segments (07,08) respectively (FEG, SLG) 
		Cast(0 as Numeric(18)) as STATS_Ttl_Education,			-- Subset of "STATS_Ttl_Accounts" - Education Segments (09,10,11,12) respectively (STE,HIE,TTE,K12E) 
		Cast(0 as Numeric(18)) as STATS_Ttl_Consumer,			-- Subset of "STATS_Ttl_Accounts" - Consumer Segments (03)(HOME)
		Cast(0 as Numeric(18)) as STATS_Ttl_Corporate,			-- Total Number of Accounts w/ Customer_Type_Code = 'CO'
		Cast(0 as Numeric(18)) as STATS_Ttl_Toshiba,			-- Total Number of Accounts w/ Customer_Type_Code = 'TO'
 
		Cast(0 as Numeric(18)) as STATS_Ttl_Buyers,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are Buyers (at least 1 RO Invoice)
		Cast(0 as Numeric(18)) as STATS_Ttl_Buyers_12Month,		-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are Buyers w/in the past 12-Months (at least 1 RO Invoice in the past 12-Months)
		Cast(0 as Numeric(18)) as STATS_Ttl_MultiBuyers,		-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are Multi Buyers (at least 2 or more RO Invoices)
		Cast(0 as Numeric(18)) as STATS_Ttl_MultiBuyers_12Month,	-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are Multi-Buyers w/in the past 12-Months (at least 2 or more RO Invoices in the past 12-Months)
		Cast(0 as Numeric(18)) as STATS_Ttl_Orders,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many RO Invoices have these Customers had	
		Cast(0 as Numeric(18)) as STATS_Ttl_Orders_12Month,		-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many RO Invoices have these Customers had w/in the past 12 Months	
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Revenue,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How much Invoice Revenue (Invoice_Amt) have these Customers had	
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Revenue_12Month,		-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How much Invoice Revenue (Invoice_Amt) have these Customers had w/in the past 12 Months	
 
		Cast(0 as Numeric(18)) as STATS_Ttl_Profile,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many have the # of Employees, Total Pc's, and SIC Abbreviation populated	
		Cast(0 as Numeric(18)) as STATS_Ttl_Email,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many have at least 1 valid Email Address on any of the Contacts or Email Addresses for the Accounts	
		Cast(0 as Numeric(18)) as STATS_Ttl_Profile_and_Email,		-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are BOTH Profiled and have EMAIL
		Cast(0 as Numeric(18)) as STATS_Ttl_ECat,			-- Of the Sales Rep's Accounts in "STATS_Ttl_Accounts" - How many are BOTH Profiled and have EMAIL
 
		Cast(0 as Numeric(18)) as STATS_Ttl_New,			-- Based on TSR1, How Many Customers placed their 1st RO Invoice during the current AOM Month time frame	
		Cast(0 as Numeric(18)) as STATS_Ttl_ReActivation,		-- Based on TSR1, Customer has an RO Invoice w/in current Month time frame, did not purchase w/in the previous 12-Month time frame, but did have an RO Invoice prior to the 12-month lapse in purchase. 
		Cast(0 as Numeric(18)) as STATS_Ttl_New_and_ReActivation, 	-- Total New Customers + Total ReActivation Customers
 
		Cast(0 as Numeric(18)) as STATS_Ttl_Days_Worked,		-- Total Days worked (obtainined from TblAgentCallData)	
		Cast(0 as Numeric(18,2)) as STATS_DaysWorked,
 
		Cast(0 as Numeric(18)) as STATS_Ttl_Book_Called,		-- Total (unique) Accounts in the Book called during the Month time frame (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
		Cast(0 as Numeric(18)) as STATS_Ttl_Book_Called_Day,		-- Total (unique) Accounts in the Book called for the Day (Record_Date)  (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
		Cast(0 as Numeric(18)) as STATS_Ttl_All_Dialed_OB,		-- From TblAgentCall data - OB Dialed Metric from existing AOM
		Cast(0 as Numeric(18)) as STATS_Ttl_Avg_Dialed_OB,		-- From TblAgentCall data - OB Avg Dialed Metric from existing AOM
		Cast(0 as Numeric(18)) as STATS_Ttl_Book_Dialed,		-- Total (non-unique) Accounts in the Book dialed during the Month time frame (i.e. If Account Dialed more than once in one day each Dial to the Accounts is counted)  (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
		Cast(0 as Numeric(18)) as STATS_Ttl_Book_Dialed_Day,		-- Total (non-unique) Accounts in the Book dialed for the Day (Record_Date) (i.e. If Account Dialed more than once in one day each Dial to the Accounts is counted)  (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
		Cast(0 as Numeric(18,2)) as STATS_Ttl_All_TalkTime_OB,		-- From TblAgentCall data - OB Talktime Metric from existing AOM  
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Avg_TalkTime_OB,		-- From TblAgentCall data - OB Avg Talktine Metric from existing AOM 
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Book_TalkTime,		-- Total Talk Time to Accounts in the Rep Book during the Month time frame (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Book_TalkTime_Day,	-- Total Talk Time to Accounts in the Rep Book for the Day (from Phone_whse..OutBound_Dials_Dto) (seconds > 60)
 
		Cast(0 as Numeric(18)) as STATS_Ttl_Qty_Overall,		-- Total Quanity awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Revenue_Overall,		-- Total Revenue awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18)) as STATS_Unit_Qty,			-- Total # of Notebook Units awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Unit_Revenue,			-- Total Notebook Revenue awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Accessory_Qty,		-- Total # of accessory Units awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Accessory_Revenue,		-- Total Accessory Revenue awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Projector_Qty,		-- Total # of Projector Units awarded to the Rep during Month time frame 
		Cast(0 as Numeric(18,2)) as STATS_Projector_Revenue,		-- Total Projector Revenue awarded to the Rep during Month time frame 
 
		Cast(0 as Numeric(18,2)) as STATS_InB_Ttl_Revenue,		-- InBound AOM Total Line Item Revenue by Rep Own (TSR2)
		Cast(0 as Numeric(18,2)) as STATS_InB_Accessory_Revenue,	-- InBound AOM Accessory Line Item Revenue by Rep Own (TSR2) NON-Category Y and W Line Items
		Cast(0 as Numeric(18,2)) as STATS_InB_Warranty_Revenue,		-- InBound AOM Warranty Line Item Revenue by Rep Own (TSR2) Category W Line Items
 
		Cast(0 as Numeric(18,2)) as STATS_InB_Booked_Count,		-- InBound AOM # Booked (From ToshibaNet's Daily Stats report)
		Cast(0 as Numeric(18,2)) as STATS_InB_Booked_Revenue,		-- InBound AOM Revenue Booked (From ToshibaNet's Daily Stats report)
		Cast(0 as Numeric(18,2)) as STATS_InB_Calls,			-- InBound AOM Inbound ACD Calls (From ToshibaNet's Daily Stats report)
 
		Cast(0 as Numeric(18,2)) as STATS_NumOfLines,			-- InBound AOM
		Cast(0 as Numeric(18,2)) as STATS_AvgLinesPerOrder,		-- Accessory AOM
					    
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Training_Hours,		-- Total # of Training Hours during the Month		Cast(0 as Numeric(18,2)) as STATS_InB_Attendance,		-- InBound AOM
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Attendance,
		Cast(0 as Numeric(18,2)) as STATS_Ttl_Tardy,
		Cast(0 as Numeric(18,2)) as STATS_Ttl_WorkTime,		
 
		Cast(0 as Numeric(18,2)) as CALCULATED_Contacts_Avg_Num,	-- (STATS_Ttl_Contacts/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Profile_Pct,		-- (STATS_Ttl_Profile_and_Email/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Book_Called_Pct,		-- (STATS_Ttl_Book_Called/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Buyer_Pct,		-- (STATS_Ttl_Buyers/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Buyer_12Month_Pct,	-- (STATS_Ttl_Buyers_12Month/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_MultiBuyer_Pct,		-- (STATS_Ttl_MultiBuyers/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_MultiBuyer_12Month_Pct,	-- (STATS_Ttl_MultiBuyers_12Month/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Avg_Book_Dialed,		-- (STATS_Ttl_Book_Dialed/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_Avg_Book_TalkTime,	-- (STATS_Ttl_Book_TalkTime/STATS_Ttl_Accounts)
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Email_Pct,		-- InBound AOM Email Pct = (STATS_Ttl_Accounts/STATS_Ttl_Email)
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Close_Pct,		-- InBound AOM
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Rev_Per_Call_Con,	-- InBound AOM
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Rev_Per_Call_SMB,	-- InBound AOM
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Revenue_Per_Order,
		Cast(0 as Numeric(18,2)) as CALCULATED_InB_Revenue_Per_Call,
		Cast(0 as Numeric(18,2)) as CALCULATED_ECat_Pct,
		Cast(0 as Numeric(18,2)) as CALCULATED_Line_Items_Per_Call,
 
		Cast(0 as Numeric(18)) as POINTS_Contacts,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Contacts(#OUTPUT_DAILY_AOM_MB1127.CALCULATED_Contacts_Avg_Num),
		Cast(0 as Numeric(18)) as POINTS_Profile_Email_Pct,		-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Profile_Email(#OUTPUT_DAILY_AOM_MB1127.CALCULATED_Profile_Pct),
		Cast(0 as Numeric(18)) as POINTS_New_ReActivation,		-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_New_ReActivation(#OUTPUT_DAILY_AOM_MB1127.STATS_Ttl_New_and_ReActivation),
		Cast(0 as Numeric(18)) as POINTS_Book_Called,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Called(#OUTPUT_DAILY_AOM_MB1127.STATS_Ttl_Book_Called),
		Cast(0 as Numeric(18)) as POINTS_Book_Called_Pct,		-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Called_Pct(#OUTPUT_DAILY_AOM_MB1127.CALCULATED_Book_Called_Pct),
		Cast(0 as Numeric(18)) as POINTS_Dialed_OB,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Dialed_OB(#OUTPUT_DAILY_AOM_MB1127.STATS_Ttl_Avg_Dialed_OB),
		Cast(0 as Numeric(18)) as POINTS_TalkTime_OB,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_TalkTime_OB(#OUTPUT_DAILY_AOM_MB1127.STATS_Ttl_Avg_TalkTime_OB),
		Cast(0 as Numeric(18)) as POINTS_Unit_Revenue,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Unit_Revenue(#OUTPUT_DAILY_AOM_MB1127.STATS_Unit_Revenue),
		Cast(0 as Numeric(18)) as POINTS_Accessory_Revenue,		-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Accessory_Revenue(#OUTPUT_DAILY_AOM_MB1127.STATS_Accessory_Revenue),
		Cast(0 as Numeric(18)) as POINTS_Training,			-- UPDATED by FUNCTION CALL Toshiba_Mkt.dbo.fn_AOM_Training(#OUTPUT_DAILY_AOM_MB1127.STATS_Ttl_Training_Hours)
		Cast(0 as Numeric(18)) as POINTS_Attendance,			
		Cast(0 as Numeric(18)) as POINTS_Tardy,				--
		Cast(0 as Numeric(18)) as POINTS_Total,				-- UPDATED based on Total Number of Accounts (see code Last Step)
 
		Cast(0 as Numeric(18)) as POINTS_InB_Accessory_Revenue,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Warranty_Revenue,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Rev_Per_Call_Con,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Rev_Per_Call_SMB,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Close_Pct,			-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Email_Pct,			-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Attendance,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_WorkTime,			-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Shipped_Revenue,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Revenue_Per_Order,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Close_Pct_SMBDev,		-- InBound AOM
		Cast(0 as Numeric(18)) as POINTS_InB_Training,	
		Cast(0 as Numeric(18)) as POINTS_InB_Total,			-- InBound AOM
 
		Cast(0 as Numeric(18)) as POINTS_ECat,				--
		Cast(0 as Numeric(18)) as POINTS_NumOfLines,			--
 
		Cast(0 as Numeric(18)) as POINTS_Accessory_Close_Pct,
		Cast(0 as Numeric(18)) as POINTS_Accessory_NumOfLines,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Revenue_Per_Call,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Training,
 
		Cast(0 as Numeric(18)) as POINTS_Accessory_Email_Pct,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Accessory_Revenue,
		Cast(0 as Numeric(18)) as POINTS_Accessory_WorkTime,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Attendance,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Warranty_Revenue,
		Cast(0 as Numeric(18)) as POINTS_Accessory_Total,
 
		@TODAY as Record_Date,
		@SOM as StartOfMonth,
		@EOM as EndOfMonth,
		@SOM_ReActivation as StartOfMonth_ForReActivation
		INTO #OUTPUT_DAILY_AOM_MB1127
		FROM Toshiba_Mkt.dbo.vSales_Rep as SLS WITH (NOLOCK) Left Outer Join 
			(
			SELECT 
			Employee.MgrUserCode,
			Manager.FirstName as ManagerFirstName,
			Manager.LastName as ManagerLastName,
			Employee.SalesNumber,
			Employee.UserCode,
			Employee.ACDID,
			Employee.FirstName as EmployeeFirstName,
			Employee.LastName as EmployeeLastName,
			Employee.Division
			FROM Toshiba_rpt.dbo.TblEmployeeActive as Employee WITH (NOLOCK) Inner Join Toshiba_rpt.dbo.TblEmployeeActive as Manager WITH (NOLOCK)
			ON (Employee.MgrUserCode = Manager.UserCode)
			WHERE (Employee.Active = 1) 
			) as #MANAGERS
		ON (SLS.Sales_Rep_Code = #MANAGERS.SalesNumber)
		ORDER BY 
		#MANAGERS.ManagerFirstName,
		#MANAGERS.ManagerLastName,
		#MANAGERS.Division,
		#MANAGERS.EmployeeFirstName,
		#MANAGERS.EmployeeLastName
 
	
		Begin
	-- b. POPULATE fields with 'NONE' values for MAX Sales Reps not listed in ToshibaNet's TblEmployeeActive
		-- Managers
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			MgrUserCode = 'NONE',
			ManagerFirstName = 'NONE',
			ManagerLastName = 'NONE'
			WHERE (MgrUserCode IS NULL)
		-- Sales Rep
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			SalesNumber = Sales_Rep_Code,
			UserCode = 'NONE',
			EmployeeFirstName = SUBSTRING(Sales_Rep_Name,1,(PATINDEX('% %',Sales_Rep_Name))),
			EmployeeLastName = SUBSTRING(Sales_Rep_Name,(PATINDEX('% %',Sales_Rep_Name))+1,50),
			Division = 'NONE'
			WHERE (UserCode IS NULL)
		End
 
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
	-- STEP 3:  REMOVE InActive Sales Reps
	-- <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><> <><><><><>  <><><><><>  <><><><><>  <><><><><>  <><><><><>
		
		Begin
			DELETE FROM #OUTPUT_DAILY_AOM_MB1127
			WHERE (Left(Sales_Rep_Name,1) = '*')
			
			DELETE FROM #OUTPUT_DAILY_AOM_MB1127
			WHERE Sales_Rep_Name = 'UNKNOWN'
			
			DELETE FROM #OUTPUT_DAILY_AOM_MB1127
			WHERE MgrUserCode = 'NONE'
			
			DELETE FROM #OUTPUT_DAILY_AOM_MB1127
			WHERE MgrUserCode NOT IN ('RAA','BVT','CAK','MHB')
 
 
		End
 
		
	
 
 
 
 
 
/*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
PART II - POPULATE STATS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
*/
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 1: Total DAYS Worked 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		-- Total Days Worked (Calculated using TblAgentCallData Sales Rep Log In's)
		-- (REQUIRED for Calculation: CALCULATED_Avg_Book_Dialed)
		-- (REQUIRED for Calculation: CALCULATED_Avg_Book_Talktime)
		
 
			UPDATE #OUTPUT_DAILY_AOM_MB1127 
			SET 
			STATS_Ttl_Days_Worked = (SELECT COUNT(*) FROM Toshiba_Rpt.dbo.tblAgentCallData WITH (NOLOCK) WHERE (Row_Date BETWEEN (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127) AND (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127)) AND #OUTPUT_DAILY_AOM_MB1127.UserCode = Toshiba_Rpt.dbo.tblAgentCallData.UserCode)
			FROM Toshiba_Rpt.dbo.tblAgentCallData WITH (NOLOCK)
			WHERE #OUTPUT_DAILY_AOM_MB1127.UserCode = Toshiba_Rpt.dbo.tblAgentCallData.UserCode
		
			-- Days Worked (from Dashboard, uses Adjustments made by Sales Rep Managers)
			-- (REQUIRED for Calculation: STATS_Ttl_Avg_TalkTime_OB)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_DaysWorked = Toshiba_Rpt.dbo.TOSH_DASHBOARD_TSR_TARGETS.DaysWorked
			FROM Toshiba_Rpt.dbo.TOSH_DASHBOARD_TSR_TARGETS
			WHERE (#OUTPUT_DAILY_AOM_MB1127.UserCode = Toshiba_Rpt.dbo.TOSH_DASHBOARD_TSR_TARGETS.TSR_UserCode)
 
		
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 2: Number of ACCOUNT and TYPES Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		-- Total ACCOUNTS 
		-- (REQUIRED for Calculation: STATS_Ttl_ECAT)
		-- (REQUIRED for Calculation: CALCULATED_Contacts_Avg_Num)
		-- (REQUIRED for Calculation: CALCULATED_Book_Called_Pct)
		-- (REQUIRED for Calculation: CALCULATED_Profile_Pct)
		
 
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Accounts = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Accounts(Sales_Rep_Code)
		
			-- Total CONTACTS 
			-- (REQUIRED for Calculation: CALCULATED_Contacts_Avg_Num)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Contacts = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Contacts(Sales_Rep_Code)
		
-- These Metrics are NOT required for AOM
IF @Run_Required_Code_Only = 'N'
BEGIN
		-- Total BUSINESS, RESELLER, GOVERNMENT, EDUCATION, and CONSUMER
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Business = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Business(Sales_Rep_Code)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Reseller = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Reseller(Sales_Rep_Code)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Government = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Government(Sales_Rep_Code)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Education = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Education(Sales_Rep_Code)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Consumer = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Consumer(Sales_Rep_Code)
	
		-- Total CORPORATE
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Corporate = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Corporate(Sales_Rep_Code)
	
		-- Total TOSHIBA
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Toshiba = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Toshiba(Sales_Rep_Code)
END
 
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 3: Account PROFILE Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		-- Total Profiled and Emailed
		-- (REQUIRED for Calculation: CALCULATED_Profile_Pct)
		
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Profile_And_Email = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Profile_And_Email(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
-- This Metric is NOT required for AOM
IF @Run_Required_Code_Only = 'N'
BEGIN
		-- Total Profiled
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Profile = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Profiled(Sales_Rep_Code)
END
 
 
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 4: Account EMAIL Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		
 
			-- Total Emailed during the time frame.
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Email = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Email(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- Total "ECR" Emails sent
			-- (REQUIRED for Calculation: CALCULATED_ECat_Pct)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_ECat = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_ECAT(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
 
 
		
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 5: ACCOUNT New/ReActiavation Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	/*
	STATS_Ttl_New
	STATS_Ttl_ReActivation
	STATS_Ttl_New_and_ReActivation
	*/
 
 
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 6: ACCOUNT Booked Count and Revenue
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		-- BOOKED COUNT
		-- (REQUIRED for Calculation: CALCULATED_Line_Items_Per_Call)
		-- (REQUIRED for Calculation: CALCULATED_InB_Close_Pct)
		-- (REQUIRED for Calculation: CALCULATED_InB_Revenue_per_Order)
		
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET
			STATS_InB_Booked_Count = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Booked_Count(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
			-- BOOKED REVENUE
			-- (REQUIRED for Calculation: CALCULATED_InB_Revenue_per_Order)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET
			STATS_InB_Booked_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Booked_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 7: ACCOUNT Call Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
			
 
			-- TOTAL INBOUND Calls
			-- (REQUIRED for Calculation: CALCULATED_InB_Per_Call)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_InB_Calls = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Calls(ACDID, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- Total CALLED 
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Book_Called = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_Called(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
				-- Total CALLED (Day)
				UPDATE #OUTPUT_DAILY_AOM_MB1127
				SET 
				STATS_Ttl_Book_Called_Day = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_Called(Sales_Rep_Code, (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())), (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())))
		
			-- Total DIALED 
	
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_All_Dialed_OB = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_All_Dialed_OB(UserCode, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Avg_Dialed_OB = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Avg_Dialed_OB_DaysWorked(UserCode, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
				UPDATE #OUTPUT_DAILY_AOM_MB1127
				SET 
				STATS_Ttl_Avg_Dialed_OB = (STATS_Ttl_All_Dialed_OB/STATS_Ttl_Avg_Dialed_OB)
				WHERE (STATS_Ttl_Avg_Dialed_OB > 0)
		
			-- Total BOOK DIALED
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Book_Dialed = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_Dialed(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
				-- Total BOOK DIALED (Day)
				UPDATE #OUTPUT_DAILY_AOM_MB1127
				SET 
				STATS_Ttl_Book_Dialed_Day = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_Dialed(Sales_Rep_Code, (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())), (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())))
		
			-- Total TALK TIME
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_All_TalkTime_OB = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_All_TalkTime_OB(UserCode, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- Average TALK TIME
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Avg_TalkTime_OB = (STATS_Ttl_All_TalkTime_OB/STATS_DaysWorked)
			WHERE STATS_DaysWorked > 0
		
		
			-- Total BOOK TALK TIME
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Book_TalkTime = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_TalkTime(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
				-- Total TALK TIME (Day)
				UPDATE #OUTPUT_DAILY_AOM_MB1127
				SET 
				STATS_Ttl_Book_TalkTime_Day = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Book_TalkTime(Sales_Rep_Code, (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())), (Toshiba_Mkt.dbo.fn_DateOnly(GetDate())))
		/*
		STATS_Ttl_Book_Called_Day
		STATS_Ttl_Book_Dialed_Day
		STATS_Ttl_Book_TalkTime_Day
		*/
 
		
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 8: ACCOUNT Buying Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
-- These Metrics are NOT required for AOM
IF @Run_Required_Code_Only = 'N'
BEGIN
		--  Total BUYERS
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET STATS_Ttl_Buyers = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Buyers(Sales_Rep_Code, '01/01/2000', Toshiba_Mkt.dbo.Fn_DateOnly(GetDate()-1))
			-- Total BUYERS (12 Month)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET STATS_Ttl_Buyers_12Month = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Buyers(Sales_Rep_Code, (SELECT MIN(StartOfMonth_ForReActivation) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		-- Total MULTI-BUYERS
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET STATS_Ttl_MultiBuyers = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_MultiBuyers(Sales_Rep_Code, '01/01/2000', Toshiba_Mkt.dbo.Fn_DateOnly(GetDate()-1))
			-- Total MULTI-BUYERS (12 Month)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET STATS_Ttl_MultiBuyers_12Month = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_MultiBuyers(Sales_Rep_Code, (SELECT MIN(StartOfMonth_ForReActivation) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		-- Total ORDERS
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET STATS_Ttl_Orders = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Orders(Sales_Rep_Code, '01/01/2000', Toshiba_Mkt.dbo.Fn_DateOnly(GetDate()-1))
			-- Total ORDERS (12 Month)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET STATS_Ttl_Orders_12Month = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Orders(Sales_Rep_Code, (SELECT MIN(StartOfMonth_ForReActivation) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		-- Total REVENUE
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET STATS_Ttl_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Revenue(Sales_Rep_Code, '01/01/2000', Toshiba_Mkt.dbo.Fn_DateOnly(GetDate()-1))
			-- Total REVENUE (12 Month)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET STATS_Ttl_Revenue_12Month = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth_ForReActivation) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
END
	
 
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 9: Account LINE ITEM Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
			
 
			-- NUMBER OF LINE ITEMS
			-- (REQUIRED for Calculation: CALCULATED_Line_Items_Per_Call)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_NumOfLines = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_NumOfLines(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
			-- ACCESSORY Reps AVERAGE LINES PER ORDER
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_AvgLinesPerOrder = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Avg_Lines_Per_Order(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		
			-- ACCESSORY Revenue
			-- (REQUIRED for Point Value: POINTS_InB_Accessory_Revenue)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_InB_Accessory_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Accessory_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- WARRANTY Revenue
			-- (REQUIRED for Point Value: POINTS_InB_Warranty_Revenue)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_InB_Warranty_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Warranty_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- REVENUE OVERALL (uses Split Logic)
			-- (REQUIRED for Point Value: POINTS_INB_Shipped_Revenue)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Revenue_Overall = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Revenue_Overall(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- UNIT REVENUE OVERALL (uses Split Logic)
			-- (REQUIRED for Point Value: POINTS_Unit_Revenue)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Unit_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Unit_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
			-- ACCESSORY REVENUE OVERALL (uses Split Logic)
			-- (REQUIRED for Point Value: POINTS_Accessory_Revenue)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Accessory_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Accessory_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
		
 
-- These Metrics are NOT required for AOM
IF @Run_Required_Code_Only = 'N'
BEGIN
		-- TOTAL Revenue
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_InB_Ttl_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Ttl_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
 
		-- QUANTITY OVERALL (uses Split Logic)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Ttl_Qty_Overall = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Ttl_Qty_Overall(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
 
		-- UNIT QUANTITY OVERALL (uses Split Logic)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Unit_Qty = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Unit_Qty(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
 
		-- ACCESSORY QUANTITY OVERALL (uses Split Logic)
 
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Accessory_Qty = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Accessory_Qty(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
 
		-- PROJECTOR QUANTITY OVERALL (uses Split Logic)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Projector_Qty = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Projector_Qty(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
	
		-- PROJECTOR REVENUE OVERALL (uses Split Logic)
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		STATS_Projector_Revenue = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_Projector_Revenue(Sales_Rep_Code, (SELECT MIN(StartOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127), (SELECT MIN(EndOfMonth) FROM #OUTPUT_DAILY_AOM_MB1127))
END
 
 
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 10: ACCOUNT Manager InPut Information 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
					
 
			-- Training Hours
			-- (REQUIRED for Point Value: POINTS_Training)
			-- (REQUIRED for Point Value: POINTS_InB_Training)
			-- (REQUIRED for Point Value: POINTS_Accessory_Training)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Training_Hours = (SELECT Sales_Rep_Training_Hours FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_TrainingHours as TRAIN_Hrs WITH (NOLOCK) WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = TRAIN_Hrs.Sales_Rep_Code AND #OUTPUT_DAILY_AOM_MB1127.StartOfMonth = TRAIN_Hrs.Training_Hours_Month) 
			FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_TrainingHours as TRAIN_Hrs WITH (NOLOCK)
			WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = TRAIN_Hrs.Sales_Rep_Code
		
			-- Work Time
			-- (REQUIRED for Point Value: POINTS_InB_WorkTime)
			-- (REQUIRED for Point Value: POINTS_Accessory_WorkTime)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_WorkTime = (SELECT Sales_Rep_WorkTime_Hours FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_WorkTime as WORKTIME WITH (NOLOCK) WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = WORKTIME.Sales_Rep_Code AND #OUTPUT_DAILY_AOM_MB1127.StartOfMonth = WORKTIME.WorkTime_Hours_Month)
			FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_WorkTime as WORKTIME WITH (NOLOCK)
			WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = WORKTIME.Sales_Rep_Code
		
			-- Attendance
			-- (REQUIRED for Point Value: POINTS_InB_Attendance)
			-- (REQUIRED for Point Value: POINTS_Accessory_Attendance)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Attendance = (SELECT Sales_Rep_Attendance_Days FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_Attendance as ATTENDANCE WITH (NOLOCK) WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = ATTENDANCE.Sales_Rep_Code AND #OUTPUT_DAILY_AOM_MB1127.StartofMonth = ATTENDANCE.Attendance_Days_Month)
			FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_Attendance as ATTENDANCE WITH (NOLOCK)
			WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = ATTENDANCE.Sales_Rep_Code
	
			-- Tardy
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			STATS_Ttl_Tardy = (SELECT Sales_Rep_Tardy_Days FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_Tardy as TARDY WITH (NOLOCK) WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = TARDY.Sales_Rep_Code AND #OUTPUT_DAILY_AOM_MB1127.StartofMonth = TARDY.Tardy_Days_Month)
			FROM TOSHIBA_Rpt.dbo.tbl_MAINTENANCE_TOSH_DAILY_AOM_Tardy as TARDY WITH (NOLOCK)
			WHERE #OUTPUT_DAILY_AOM_MB1127.Sales_Rep_Code = TARDY.Sales_Rep_Code
			
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- STEP 11: POPULATE the CALCULATED Fields
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
 
		
 
			-- (REQUIRED for Point Value: POINTS_Contacts)
			-- (REQUIRED for Point Value: POINTS_Profile_Email_Pct)
			-- (REQUIRED for Point Value: POINTS_Book_Called_Pct)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
	
			SET 
			CALCULATED_Contacts_Avg_Num = (STATS_Ttl_Contacts/STATS_Ttl_Accounts),
			CALCULATED_Profile_Pct = (((CAST(STATS_Ttl_Profile_And_Email as FLOAT))/(CAST(STATS_Ttl_Accounts as FLOAT))) * 100),
			CALCULATED_Book_Called_Pct = ((CAST(STATS_Ttl_Book_Called as FLOAT)/CAST(STATS_Ttl_Accounts as FLOAT)) * 100)
			WHERE (STATS_Ttl_Accounts > 0)
	
			-- (REQUIRED for Point Value: POINTS_InB_Email_Pct)	
			-- (REQUIRED for Point Value: POINTS_Accessory_Email_Pct)
			/*
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_InB_Email_Pct = ((CAST(STATS_Ttl_Email as Float)/CAST(STATS_Ttl_Accounts as Float)) * 100)
			WHERE (STATS_Ttl_Email > 0)
			*/
	
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_InB_Email_Pct = Toshiba_Mkt.dbo.fn_TOSH_AOM_STATS_InB_Email_Pct(Sales_Rep_Code)
	
	
		
			-- (REQUIRED for Point Value: POINTS_InB_Close_Pct)
			-- (REQUIRED for Point Value: POINTS_Accessory_Close_Pct)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_InB_Close_Pct = ((CAST(STATS_InB_Booked_Count as Float)/CAST(STATS_InB_Calls as Float)) * 100)
			WHERE (STATS_InB_Calls > 0)
		
			-- (REQUIRED for Point Value: POINTS_INB_Revenue_Per_Order)
			-- (REQUIRED for Point Value: POINTS_Accessory_Revenue_Per_Call)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_InB_Revenue_Per_Order = (STATS_InB_Booked_Revenue/STATS_InB_Booked_Count)
			WHERE (STATS_InB_Booked_Count > 0)
				UPDATE #OUTPUT_DAILY_AOM_MB1127
				SET 
				CALCULATED_InB_Revenue_Per_Call = (STATS_InB_Booked_Revenue/STATS_InB_Calls)
				WHERE (STATS_InB_Calls > 0)
	
		
			-- (REQUIRED for Point Value: POINTS_ECat)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_ECat_Pct = (CAST(STATS_Ttl_ECAT as FLOAT)/CAST(STATS_Ttl_Accounts as FLOAT))*100
			WHERE (STATS_Ttl_Accounts > 0)
	
			-- (REQUIRED for Point Value: POINTS_Accessory_NumOfLines)
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET 
			CALCULATED_Line_Items_Per_Call = (STATS_NumOfLines/STATS_InB_Booked_Count)
			WHERE (STATS_InB_Booked_Count > 0)
	
		
 
IF @Run_Required_Code_Only = 'N'
BEGIN
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		CALCULATED_Buyer_Pct = (STATS_Ttl_Buyers/STATS_Ttl_Accounts),
		CALCULATED_Buyer_12Month_Pct = (STATS_Ttl_Buyers_12Month/STATS_Ttl_Accounts),
		CALCULATED_MultiBuyer_Pct = (STATS_Ttl_MultiBuyers/STATS_Ttl_Accounts),
		CALCULATED_MultiBuyer_12Month_Pct = (STATS_Ttl_MultiBuyers_12Month/STATS_Ttl_Accounts)
		WHERE (STATS_Ttl_Accounts > 0)
 
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		CALCULATED_Avg_Book_Dialed = (STATS_Ttl_Book_Dialed/STATS_Ttl_Days_Worked),
		CALCULATED_Avg_Book_TalkTime = (STATS_Ttl_Book_TalkTime/STATS_Ttl_Days_Worked)
		WHERE (STATS_Ttl_Days_Worked > 0)
 
END
 
 
/*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
PART III - ASSIGN AOM POINTS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
*/
 
	-- a. ASSIGN Points for each criteria in the AOM OUTBOUND Points Matrix
	
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		POINTS_Unit_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Unit_Revenue(STATS_Unit_Revenue),		
		POINTS_TalkTime_OB = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_TalkTime_OB(STATS_Ttl_Avg_TalkTime_OB),
		POINTS_Dialed_OB = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Dialed_OB(STATS_Ttl_Avg_Dialed_OB),
		POINTS_Contacts = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Contacts(CALCULATED_Contacts_Avg_Num),
		POINTS_Accessory_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Accessory_Revenue(STATS_Accessory_Revenue),
		POINTS_Book_Called = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Called(STATS_Ttl_Book_Called),
		POINTS_Book_Called_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Called_Pct(CALCULATED_Book_Called_Pct),
		POINTS_Profile_Email_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Profile_Email(CALCULATED_Profile_Pct),
		POINTS_ECat = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_ECat(CALCULATED_ECat_Pct),
		POINTS_Tardy = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Tardy(STATS_Ttl_Tardy),
		POINTS_Training  = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_OutBound_Training(STATS_Ttl_Training_Hours)
	
		
 
	-- b. ASSIGN "TOTAL AOM POINTS" (there's a condition of which Point Value is used based on Total # of Accounts Owned by the Rep)
		
		-- IF Sales Rep has LESS than 500 Accounts use "POINTS_Called_Pct"
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET POINTS_Total = (POINTS_Unit_Revenue + POINTS_TalkTime_OB + POINTS_Dialed_OB + POINTS_Contacts + POINTS_Accessory_Revenue + POINTS_Book_Called_Pct + POINTS_Profile_Email_Pct + POINTS_ECat + POINTS_Tardy + POINTS_Training)
			WHERE STATS_Ttl_Accounts < 500
 
		-- IF Sales Rep has MORE than 500 Accounts use "POINTS_Called"
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET POINTS_Total = (POINTS_Unit_Revenue + POINTS_TalkTime_OB + POINTS_Dialed_OB + POINTS_Contacts + POINTS_Accessory_Revenue + POINTS_Book_Called + POINTS_Profile_Email_Pct + POINTS_ECat + POINTS_Tardy + POINTS_Training)
			WHERE STATS_Ttl_Accounts > 500
 
		--End
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- b. ASSIGN Points for each criteria in the AOM INBOUND Points Matrix
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		
		-- <><><><><> <><><><><> <><><><><>
		-- INBOUND Points
		-- <><><><><> <><><><><> <><><><><>
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		POINTS_INB_Shipped_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Shipped_Revenue(STATS_Ttl_Revenue_Overall),
		POINTS_InB_WorkTime = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_WorkTime(STATS_Ttl_WorkTime),
		POINTS_InB_Close_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Close(CALCULATED_InB_Close_Pct),
		POINTS_INB_Revenue_Per_Order = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Revenue_Per_Call(CALCULATED_InB_Revenue_Per_Call),
		POINTS_InB_Accessory_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Accessory_Revenue(STATS_InB_Accessory_Revenue),
		POINTS_InB_Warranty_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Warranty_Revenue(STATS_InB_Warranty_Revenue),
		POINTS_InB_Attendance = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Attendance(STATS_Ttl_Attendance),
		POINTS_InB_Email_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Email(CALCULATED_InB_Email_Pct),
		POINTS_InB_Training  = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_InBound_Training(STATS_Ttl_Training_Hours) 
		WHERE (MgrUserCode IN ('CAK','MHB','BVT') AND Division IN ('InBound','SMB Inbound')) AND (Left(Sales_Rep_Name,1) <> '*') 
			-- INBOUND Point Totals
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET #OUTPUT_DAILY_AOM_MB1127.POINTS_InB_Total = (POINTS_INB_Shipped_Revenue + POINTS_InB_WorkTime + POINTS_InB_Close_Pct + POINTS_INB_Revenue_Per_Order + POINTS_InB_Accessory_Revenue + POINTS_InB_Warranty_Revenue + POINTS_InB_Attendance + POINTS_InB_Email_Pct + POINTS_InB_Training) 
			WHERE (MgrUserCode IN ('CAK','MHB','BVT') AND Division IN ('InBound','SMB Inbound')) AND (Left(Sales_Rep_Name,1) <> '*') 
		
 
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
	-- c. ASSIGN Points for each criteria in the AOM ACCESSORY Points Matrix
	-- <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><> <><><><><>
		
		-- <><><><><> <><><><><> <><><><><>
		-- ACCESSORY Points
		-- <><><><><> <><><><><> <><><><><>
		UPDATE #OUTPUT_DAILY_AOM_MB1127
		SET 
		POINTS_Accessory_Close_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Close(CALCULATED_InB_Close_Pct),
		POINTS_Accessory_NumOfLines = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_NumOfLines(STATS_AvgLinesPerOrder),
		POINTS_Accessory_Revenue_Per_Call = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Revenue_Per_Call(CALCULATED_InB_Revenue_per_Order),
		POINTS_Accessory_Training  = dbo.fn_AOM_2007_POINTS_Accessory_Training(STATS_Ttl_Training_Hours), 
		POINTS_Accessory_Email_Pct = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Email(CALCULATED_InB_Email_Pct),
		POINTS_Accessory_Accessory_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Accessory_Revenue(STATS_InB_Accessory_Revenue),
		POINTS_Accessory_WorkTime = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_WorkTime(STATS_Ttl_WorkTime),
		POINTS_Accessory_Warranty_Revenue = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Warranty_Revenue(STATS_InB_Warranty_Revenue),
		POINTS_Accessory_Attendance = Toshiba_Mkt.dbo.fn_AOM_2007_POINTS_Accessory_Attendance(STATS_Ttl_Attendance)
		WHERE (MgrUserCode IN ('MHB') AND Division = 'Accessories') AND (Left(Sales_Rep_Name,1) <> '*') 
			-- ACCESSORY Point Totals
			UPDATE #OUTPUT_DAILY_AOM_MB1127
			SET POINTS_Accessory_Total = (POINTS_Accessory_Close_Pct + POINTS_Accessory_Revenue_Per_Call + POINTS_Accessory_NumOfLines + POINTS_Accessory_Training + POINTS_Accessory_Email_Pct + POINTS_Accessory_Accessory_Revenue + POINTS_Accessory_WorkTime + POINTS_Accessory_Warranty_Revenue + POINTS_Accessory_Attendance) 
			WHERE (MgrUserCode IN ('MHB') AND Division = 'Accessories') AND (Left(Sales_Rep_Name,1) <> '*') 
 
		
 
 
/*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
PART vI - LOAD Data into AOM Table
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
*/
	Begin
		INSERT INTO TOSH_DAILY_AOM_2007_MPBTestTable
		SELECT * FROM #OUTPUT_DAILY_AOM_MB1127
	End
 
Commit Transaction 
 
 
/*
SELECT 
Record_Date, 
COUNT(*) 
FROM TOSH_DAILY_AOM_2007_MPBTestTable
GROUP BY Record_Date
ORDER BY Record_Date desc
*/
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

0
Comment
Question by:tcgeeks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 20379354
Hi,

A couple of first thoughts. Not sure they'll add that much.

There is no set nocount on early in the procedure. This helps reduce the number of round-trips between client and server.

What is your fn_DateOnly function? One of the quickest is this one
dateadd( day, datediff( day, 0, getdate() ), 0 )

It can be used to find the start of the month.
set @StartOfThisMonth = dateadd( month, datediff( month, 0, getdate() ), 0 )

I suggest that you create a log table and insert something into this after each major peice of code, and include the current datetime. That can start to show time for each step to run and point you to what is taking all the time. Include the @@rowcount for the number of rows affected.

In lines 102 - 228 you are casting everything to numeric. Why? My concern is that with a large number of columns (which appears very wide) and a large number of rows, you have a floating point operation that can be quite slow.

Lines 260 - 278 look nice, but what do they achieve at this point -

Lines 284 - 298 remove inactive Sales Reps - see if you can write some of these conditions into the select (starting line 84) where clause, and you'll a) speed up that select, b) speed up the insert, c) eliminate much of the update in lines 260 - 278 because those rows wont be there.

From line 290 - 650 you are repeatedly calculating min( StartOfMonth ) and min( EndOfMonth ). Calculate these values once and store in a variable, and use the variable.

This prcedure uses a who bunch of functions that make coming up with anything specific quite hard. Generally there are a few things that are best practice, but I don't know if that would help much.

The best I can suggest is to start logging in selected spots, and or using SQL profiler and the query plans to find the most expensive parts of this procedure.

Otherwise, its plainly just one of those things that is going to take a small while to run.

Tuning tempdb might help some - tempdb data file on its own raid, logs and data on their own raid, backup files on their own raid, OS on their own raid ...etc.

Cheers
  David
0
 

Author Comment

by:tcgeeks
ID: 20382880
David,
David,
Thank you for the advise.  I have tried some of the solutions you mention and could only reduce processing time by 45 minutes.  Basically the fn_DateOnly is just formatting.  The procuedures produces a report for sales reps by manager and deals a lot with talk time number which is the reason so many fields are cast.  Unfortunately, this procedure runs daily.  Anyway, I will implement your suggestions that I did not come up with.

Thank you,
tcgeeks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question