/* Active Transferee Report — TE counts (ReloAction) Distinct TEFileNo counts by CorpCode / MoveType / Policy (sample aggregations). Same universe/filters as 01 Active TE Count Detail.sql. Run on ReloAction. */ DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @ProfitCenter varchar(10) = NULL; DECLARE @CAC varchar(10) = NULL; DECLARE @SrvcProvider varchar(4) = NULL; DECLARE @AuthorizedBy int = 0; DECLARE @UserType smallint = 0; DECLARE @Months int = 0; DECLARE @VIPOnly bit = 0; DECLARE @IncludeREM bit = 1; DECLARE @CutoffDate datetime = DATEADD(month, -@Months, CAST(GETDATE() AS date)); SELECT RTRIM(a.CorpCode) AS CorpCode, a.FormalName, a.moveTypeDescription AS MoveTypeDescription, a.ReloPolicy, COUNT(DISTINCT a.TEFileNo) AS ActiveTECount FROM dbo.vwActiveTransferees a INNER JOIN dbo.tblTEMain1 m1 ON a.TEFileNo = m1.TEFileNo WHERE a.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTold) AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND a.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND a.CorpCode = @CorpCode) ) AND (NULLIF(@ProfitCenter, '') IS NULL OR a.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR a.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR a.SrvcProvider = @SrvcProvider) AND (@AuthorizedBy = 0 OR a.ContactID = @AuthorizedBy) AND (@UserType = 0 OR a.UserType = @UserType) AND (@Months = 0 OR a.AuthDate <= @CutoffDate) AND (@VIPOnly = 0 OR ISNULL(m1.ExecPgm, 0) = 1) AND (@IncludeREM = 1 OR ISNULL(a.REMService, 0) = 0) GROUP BY RTRIM(a.CorpCode), a.FormalName, a.moveTypeDescription, a.ReloPolicy ORDER BY CorpCode, MoveTypeDescription, ReloPolicy;