Hall of Fame
1. aneeshatti...1,603,632
2. rrjegan171,513,636
3. angelIII1,457,953
4. chapmandew927,604
5. RiteshShah654,191
6. BrandonG...623,489
7. mark_wills573,509
8. reb73484,935
9. mwvisa1472,716
10. acperkins420,181
11. ralmada387,102
12. CGLuttrell386,285
13. matthews...310,006
14. Sharath_...300,365
15. JestersGrind231,831
16. tigin44219,338
17. pssandhu211,636
18. EugeneZ202,754
19. udayakum...200,308
20. momi_sa...194,257
21. pcelba193,296
22. DanielWilson190,783
23. prabhakar...186,110
24. St3veMax182,397
25. brejk181,029
1. angelIII5,045,718
2. chapmandew3,728,125
3. aneeshatti...2,750,338
4. rrjegan171,513,636
5. mark_wills1,403,775
6. BrandonG...1,324,338
7. mwvisa1920,172
8. acperkins816,148
9. momi_sa...681,226
10. RiteshShah654,191
11. matthews...610,554
12. appari586,119
13. dtodd555,876
14. dqmq547,704
15. ptjcb545,683
16. Yveau539,545
17. DanielWilson521,282
18. EugeneZ502,048
19. reb73484,935
20. brejk449,023
21. PFrog404,802
22. PedroCGD401,974
23. ralmada389,102
24. CGLuttrell386,285
25. imitchie352,938
	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
	[x] Attachment Details
[x]
The Solution Rating System
With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community
Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.
If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.
Thank you!
9.2
Need to optimize stored proc- takes 8 hours to run

Asked by tcgeeks in SQL Server 2005

Tags: SQL 2000, IE 6.0

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
Get your answer NOW
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:
633:
634:
635:
636:
637:
638:
639:
640:
641:
642:
643:
644:
645:
646:
647:
648:
649:
650:
651:
652:
653:
654:
655:
656:
657:
658:
659:
660:
661:
662:
663:
664:
665:
666:
667:
668:
669:
670:
671:
672:
673:
674:
675:
676:
677:
678:
679:
680:
681:
682:
683:
684:
685:
686:
687:
688:
689:
690:
691:
692:
693:
694:
695:
696:
697:
698:
699:
700:
701:
702:
703:
704:
705:
706:
707:
708:
709:
710:
711:
712:
713:
714:
715:
716:
717:
718:
719:
720:
721:
722:
723:
724:
725:
726:
727:
728:
729:
730:
731:
732:
733:
734:
735:
736:
737:
738:
739:
740:
741:
742:
743:
744:
745:
746:
747:
748:
749:
750:
751:
752:
753:
754:
755:
756:
757:
758:
759:
760:
761:
762:
763:
764:
765:
766:
767:
768:
769:
770:
771:
772:
773:
774:
775:
776:
777:
778:
779:
780:
781:
782:
783:
784:
785:
786:
787:
788:
789:
790:
791:
792:
793:
794:
795:
796:
797:
798:
799:
800:
801:
802:
803:
804:
805:
806:
807:
808:
809:
810:
811:
812:
813:
814:
815:
816:
817:
818:
819:
820:
821:
822:
823:
824:
825:
826:
827:
828:
829:
830:
831:
832:
833:
834:
835:
836:
837:
838:
839:
840:
841:
842:
843:
844:
845:
846:
847:
848:
849:
850:
851:
852:
853:
854:
855:
856:
857:
858:
859:
860:
861:
862:
863:
864:
865:
866:
867:
868:
869:
870:
871:
872:
873:
874:
875:
876:
877:
878:
879:
880:
881:
882:
883:
884:
885:
886:
887:
888:
889:
890:
891:
892:
893:
894:
895:
896:
897:
898:
899:
900:
901:
902:
903:
904:
905:
906:
907:
908:
909:
910:
911:
912:
913:
914:
915:
916:
917:
918:
919:
920:
921:
922:
923:
924:
925:
926:
927:

           
           
             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

           
         
20091111-EE-VQP-89 / EE_QW_2_20070628
Need to optimize stored proc- takes 8 hours to run

Asked by tcgeeks in SQL Server 2005

Tags: SQL 2000, IE 6.0

About this solution
