Solved

Need to optimize stored proc- takes 8 hours to run

Posted on 2007-11-29
2
132 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
2 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2000:  Shrink LDF file 11 32
SQL Select * from 6 29
SQL help 5 46
Sql Permission 6 42
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now