gistfile1.txt

WITH ETACTIVITY AS (
		select 
			'Event' SourceTbl ,evt.AccountId AccountId ,evt.Id Id 
			,evt.ActivityDate ActivityDate ,evt.OwnerId OwnerId 
			-- Script to replace condition with Filters
			,CASE WHEN (1=1  AND (00=11  OR 1=1  )  AND evt.[Type] IN ('Demo (F2F)','Meeting (F2F)','Web Preso/Demo (virtual F2F)')  ) THEN 1 ELSE 0 END Filtered
			
		from dbo.[Event] evt (nolock)
		where 
			-- First of month less a year
			evt.ActivityDate >= DATEADD(m, -12, CURRENT_TIMESTAMP)
			-- pClosedOnly_Event
			--AND ( evt.result__c  IN ('Completed','Attended') AND evt.Result__c IS NOT NULL )
			AND evt.AccountId IS NOT NULL
			
		Union All
		
		select 
			'Task' SourceTbl ,tsk.AccountId AccountId ,tsk.Id Id 
			,CASE WHEN tsk.ActivityDate IS NULL THEN tsk.CreatedDate ELSE tsk.ActivityDate END ActivityDate ,tsk.OwnerId OwnerId 
			-- Script to replace condition with Filters
			,CASE WHEN (2=2  AND (00=11  OR 1=1  )  AND tsk.[Type] IN ('Demo (F2F)','Meeting (F2F)','Web Preso/Demo (virtual F2F)')  ) THEN 1 ELSE 0 END Filtered
			
		from dbo.Task tsk (nolock)
		where 
			-- First of month less a year
			( tsk.ActivityDate >= DATEADD(m, -12, CURRENT_TIMESTAMP)
				OR ( tsk.ActivityDate IS NULL AND tsk.CreatedDate >= DATEADD(m, -12, CURRENT_TIMESTAMP)	)
			)
			-- pClosedOnly_Task
			--AND tsk.IsClosed = 'true'
			AND tsk.AccountId IS NOT NULL
	)
	, AccountHierarchy AS (
		SELECT a1.Id
			,a1.ParentId 
			,CAST(Id as varchar(255)) SortCol
		FROM Account a1 (nolock) 
		-- Replace in script (Wells Fargo & Company)
		WHERE  Top_10__c = 'true' 
			-- ALL = a1.Top_10__c
  
		UNION ALL 
      
		SELECT act.Id
			,act.ParentId
			,CAST(h.SortCol + act.Id as varchar(255)) SortCol
		FROM Account act (nolock)  
			INNER JOIN AccountHierarchy h 
			ON act.ParentId = h.Id
		WHERE 3=3
		-- TargetOnly == no return from Sub
	)
	, AccountTeamRollup AS (
		-- EG Account Executive only?
		Select distinct atm1.AccountId, CAST( '' AS VARCHAR(8000) ) UserN, CAST( '' AS VARCHAR(8000) ) lastUser, 0 cnt
		From AccountTeamMember atm1
		
		UNION ALL
		
		Select atm2.AccountId
			, CAST( atr.UserN  + ', ' + u.Name  AS VARCHAR(8000) ) UserN
			, CAST( u.Name AS VARCHAR(8000) ) lastUser
			, atr.cnt + 1
		From AccountTeamMember atm2
			INNER JOIN AccountTeamRollup atr
			ON atm2.AccountId = atr.AccountId
			INNER JOIN [User] u  (nolock) 
			ON atm2.UserId = u.Id
		Where u.Name > atr.lastUser
	)
	
SELECT 
	acct.Id Acc__ID ,acct.Name Acc__Name , ad.Lifetime_Spend__c Acc__LifeSpend  ,acct.BillingState Acc__State
	,acct.Industry Acc__Industry ,acct.NumberOfEmployees Acc__Employees
	
	,(SELECT b.UserN
		from ( select a.AccountId, SUBSTRING(a.UserN,3,LEN(a.UserN)) UserN, RANK() OVER ( PARTITION BY a.AccountId ORDER BY a.cnt DESC ) rnk
			from AccountTeamRollup a
			where a.AccountId = acct.Id ) b
		where rnk = 1) Team
	
	,ad.TotalOpenOpportunities Acc__OpenOpp ,ad.TotalAmount Acc__OpenAmnt	
	,ad.Trail12Mo Acc__Trail12 ,ad.Trail12Mo_MR Acc__Trail12MR ,ad.Trail36Mo Acc__Trail36
	
	, 'abc' SortCol 
	, 'All Targets' Top_Name 

	,ISNULL(acctActv.QCurr,0) Acc__QCurr
	,ISNULL(acctActv.YCurr,0) Acc__YCurr
	
	,ISNULL(acctActv.QCurr_fltr,0) Fltr__QCurr
	,ISNULL(acctActv.YCurr_fltr,0) Fltr__YCurr
	
-- (NO LOCK) THE JOINS per Satish
FROM 
	dbo.Account acct (nolock)
	
	-- precomputed Account Open Opportunity and Trailing Spend SUM()s
	 
	 LEFT OUTER JOIN (  SELECT LEFT(ahad.SortCol,18) AccountId  	,SUM(acctad.Lifetime_Spend__c) Lifetime_Spend__c  	,SUM(ad.TotalOpenOpportunities) TotalOpenOpportunities ,SUM(ad.TotalAmount) TotalAmount  	,SUM(ad.Trail12Mo) Trail12Mo ,SUM(ad.Trail12Mo_MR) Trail12Mo_MR ,SUM(ad.Trail36Mo) Trail36Mo  FROM dbo.AccountDetail ad (nolock)  	INNER JOIN AccountHierarchy ahad ON ad.AccountId = ahad.Id 		INNER JOIN Account acctad (nolock) ON acctad.Id = ahad.Id  GROUP BY LEFT(ahad.SortCol,18)  ) ad  ON ad.AccountId = acct.Id 	
	
	  
	  
	
	-- Get Account Activity SUMs
	LEFT OUTER JOIN (
		 SELECT LEFT(ah2.SortCol,18) AccountId 
		,SUM( CASE WHEN eta.ActivityDate >= DATEADD(d, -90, CURRENT_TIMESTAMP) 			        
			THEN 1 ELSE 0 END ) QCurr
		,COUNT( * ) YCurr
		
		,SUM( CASE WHEN eta.ActivityDate >= DATEADD(d, -90, CURRENT_TIMESTAMP) 
			        AND Filtered = 1
			THEN 1 ELSE 0 END ) QCurr_fltr
		,SUM( CASE WHEN Filtered = 1
			THEN 1 ELSE 0 END ) YCurr_fltr
		
		FROM 
			ETACTIVITY eta (nolock)
		 INNER JOIN AccountHierarchy ah2 ON eta.AccountId = ah2.Id GROUP BY LEFT(ah2.SortCol,18)
	) acctActv
	ON acct.Id = acctActv.AccountId
	
Where  acct.Top_10__c = 'true'