We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

;WITH PartialAgg AS (
	SELECT 
		T.CompanyID, 
		MAX(UD.UserCount) 
	FROM TargetsBeforeCurrentCriterion AS T 
	INNER JOIN new.CompanyIndex AS CI 
		ON CI.CompanyID = T.CompanyID
	INNER JOIN UserData.dbo.CriteriaDistribution AS CD 
		ON [CD].[SegmentNo] = CI.SegmentNo 
		AND CI.[CriteriaID] = CD.[CriteriaID] 

	LEFT OUTER JOIN (
		SELECT CompanyID, UserCount = COUNT(UserDataID)  
		FROM [UserData].dbo.[UserData] 
		WHERE [LocalIdentifierID] = @LocalIdentifierID 
			AND [IsActive] = 1 
		GROUP BY CompanyID
	) UD ON T.CompanyID = UD.CompanyID

	WHERE [LocalIdentifierID] = 1 
		AND CD.[CriteriaID] = 1 
		AND isExcluded = 'FALSE'
	GROUP BY T.CompanyID
)
SELECT COUNT(CompanyID), 
	SUM(UserDataID)
FROM PartialAgg

;With Cte_Newcompany(NewCompanyId)
AS 
(
	SELECT CompanyID 
	FROM new.CompanyIndex AS CI 
	INNER JOIN UserData.dbo.CriteriaDistribution AS CD 
		ON CI.SegmentNo =[CD].[SegmentNo] 
			AND CI.[CriteriaID] = CD.[CriteriaID] 
			AND CD.[CriteriaID] = 1 
	WHERE [LocalIdentifierID] = 1 
		AND isExcluded = 'FALSE'
)
Select 
	Count(t.companyId)
	,Count(UserDataId)
From
	TargetsBeforeCurrentCriterion AS T
	INNER JOIN Cte_Newcompany Nc 
		on	t.CompanyId=Nc.NewCompanyId
	LEFT OUTER JOIN [UserData].dbo.[UserData] as UD
		ON t.CompanyId=ud.CompanyId
			AND ud.IsActive=1
where 
	[UD].[LocalIdentifierID] = @LocalIdentifierID

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass