/* Ranked Client Revenue — Service Groups (DatawarehouseNCM) ========================================================= BO: frmRankedClientRevenue (OptServiceGroups) Crystal: RankedClientRevenueTop100 / TopAll / Alpha (.rpt) Fact: dbo.FactRankedClientRevenue Run on DatawarehouseNCM after populate. Profit center omitted. Form fields returned in SELECT for filter/group. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @ReportType varchar(10) = 'Regular'; -- Regular | CRD DECLARE @MarketingCRDFilter varchar(10) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @IncludeDirectCosts bit = 0; DECLARE @GroupBy varchar(20) = 'ServiceGroups'; DECLARE @SortBy varchar(20) = 'Top100'; -- Alphabetical | Top25 | Top50 | Top100 | TopAll DECLARE @RankByYear varchar(10) = 'Current'; -- Current | Prior DECLARE @RankByColumn varchar(20) = 'Total'; -- Departure | Destination | International | AdminTools | Mortgage | Total DECLARE @PriorStart datetime = DATEADD(YEAR, -1, @StartDate); DECLARE @PriorEnd datetime = DATEADD(YEAR, -1, @EndDate); ;WITH Rev AS ( SELECT RTRIM(f.CorpCode) AS CorpCode, f.TrxDate, f.Amount, f.ServiceCode, /* BO Service Groups rule: ServiceType = 2 rolls into INTL */ CASE WHEN f.ServiceType = 2 THEN 'INTL' ELSE f.ServiceGroupCode END AS ServiceGroupCode FROM dbo.FactRankedClientRevenue f WHERE f.TrxDate IS NOT NULL AND ( (f.TrxDate BETWEEN @StartDate AND @EndDate) OR (f.TrxDate BETWEEN @PriorStart AND @PriorEnd) ) AND ( @IncludeDirectCosts = 1 OR ISNULL(f.IsDirectCost, 0) = 0 ) ), ByClient AS ( SELECT CorpCode, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS CurrDept, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS PriorDept, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS CurrDest, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS PriorDest, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'INTL' THEN Amount ELSE 0 END) AS CurrIntl, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'INTL' THEN Amount ELSE 0 END) AS PriorIntl, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS CurrAdmn, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS PriorAdmn, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'MORT' THEN Amount ELSE 0 END) AS CurrMort, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'MORT' THEN Amount ELSE 0 END) AS PriorMort, SUM(CASE WHEN TrxDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode NOT IN ('DEPT', 'DEST', 'INTL', 'ADMN', 'MORT') THEN Amount ELSE 0 END) AS CurrOther, SUM(CASE WHEN TrxDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode NOT IN ('DEPT', 'DEST', 'INTL', 'ADMN', 'MORT') THEN Amount ELSE 0 END) AS PriorOther FROM Rev WHERE RTRIM(ISNULL(CorpCode, '')) <> '' GROUP BY CorpCode ), Rolled AS ( SELECT CASE WHEN @ParentRollup = 1 THEN ISNULL(NULLIF(RTRIM(c.ParentCompany), ''), RTRIM(b.CorpCode)) ELSE RTRIM(b.CorpCode) END AS CorpCode, MAX(RTRIM(c.ParentCompany)) AS ParentCompany, SUM(b.CurrDept) AS CurrDept, SUM(b.PriorDept) AS PriorDept, SUM(b.CurrDest) AS CurrDest, SUM(b.PriorDest) AS PriorDest, SUM(b.CurrIntl) AS CurrIntl, SUM(b.PriorIntl) AS PriorIntl, SUM(b.CurrAdmn) AS CurrAdmn, SUM(b.PriorAdmn) AS PriorAdmn, SUM(b.CurrMort) AS CurrMort, SUM(b.PriorMort) AS PriorMort, SUM(b.CurrOther) AS CurrOther, SUM(b.PriorOther) AS PriorOther FROM ByClient b LEFT JOIN dbo.DimCorpClient c ON RTRIM(c.CorpCode) = RTRIM(b.CorpCode) GROUP BY CASE WHEN @ParentRollup = 1 THEN ISNULL(NULLIF(RTRIM(c.ParentCompany), ''), RTRIM(b.CorpCode)) ELSE RTRIM(b.CorpCode) END ), WithAttrs AS ( SELECT r.*, cc.FormalName, cc.MarketingCRD, i.LastName AS CRDLastName, i.FirstName AS CRDFirstName, CASE @RankByColumn WHEN 'Departure' THEN CASE WHEN @RankByYear = 'Prior' THEN r.PriorDept ELSE r.CurrDept END WHEN 'Destination' THEN CASE WHEN @RankByYear = 'Prior' THEN r.PriorDest ELSE r.CurrDest END WHEN 'International' THEN CASE WHEN @RankByYear = 'Prior' THEN r.PriorIntl ELSE r.CurrIntl END WHEN 'AdminTools' THEN CASE WHEN @RankByYear = 'Prior' THEN r.PriorAdmn ELSE r.CurrAdmn END WHEN 'Mortgage' THEN CASE WHEN @RankByYear = 'Prior' THEN r.PriorMort ELSE r.CurrMort END ELSE CASE WHEN @RankByYear = 'Prior' THEN r.PriorDept + r.PriorDest + r.PriorIntl + r.PriorAdmn + r.PriorMort ELSE r.CurrDept + r.CurrDest + r.CurrIntl + r.CurrAdmn + r.CurrMort END END AS RankAmount FROM Rolled r LEFT JOIN dbo.DimCorpClient cc ON RTRIM(cc.CorpCode) = RTRIM(r.CorpCode) LEFT JOIN dbo.DimInitials i ON RTRIM(i.Initials) = RTRIM(cc.MarketingCRD) ), Filtered AS ( SELECT * FROM WithAttrs WHERE @ReportType <> 'CRD' OR ( (@MarketingCRDFilter IS NULL AND MarketingCRD IS NOT NULL AND RTRIM(MarketingCRD) <> '') OR (@MarketingCRDFilter = '' AND (MarketingCRD IS NULL OR RTRIM(MarketingCRD) = '')) OR (@MarketingCRDFilter IS NOT NULL AND @MarketingCRDFilter <> '' AND RTRIM(MarketingCRD) = RTRIM(@MarketingCRDFilter)) ) ), Ranked AS ( SELECT f.*, CASE WHEN @SortBy = 'Alphabetical' THEN NULL ELSE ROW_NUMBER() OVER (ORDER BY f.RankAmount DESC, f.CorpCode) END AS ClientRank FROM Filtered f ) SELECT @StartDate AS StartDate, @EndDate AS EndDate, @ReportType AS ReportType, @MarketingCRDFilter AS MarketingCRDFilter, @ParentRollup AS ParentRollup, @IncludeDirectCosts AS IncludeDirectCosts, @GroupBy AS GroupBy, @SortBy AS SortBy, @RankByYear AS RankByYear, @RankByColumn AS RankByColumn, r.CorpCode, r.ParentCompany, r.FormalName, r.MarketingCRD, r.CRDLastName, r.CRDFirstName, r.ClientRank, r.RankAmount, r.RankAmount / 1000.0 AS RankAmountInThousands, r.CurrDept, r.CurrDest, r.CurrIntl, r.CurrAdmn, r.CurrMort, r.CurrOther, r.CurrDept / 1000.0 AS CurrDeptInThousands, r.CurrDest / 1000.0 AS CurrDestInThousands, r.CurrIntl / 1000.0 AS CurrIntlInThousands, r.CurrAdmn / 1000.0 AS CurrAdmnInThousands, r.CurrMort / 1000.0 AS CurrMortInThousands, r.CurrOther / 1000.0 AS CurrOtherInThousands, r.PriorDept, r.PriorDest, r.PriorIntl, r.PriorAdmn, r.PriorMort, r.PriorOther, r.PriorDept / 1000.0 AS PriorDeptInThousands, r.PriorDest / 1000.0 AS PriorDestInThousands, r.PriorIntl / 1000.0 AS PriorIntlInThousands, r.PriorAdmn / 1000.0 AS PriorAdmnInThousands, r.PriorMort / 1000.0 AS PriorMortInThousands, r.PriorOther / 1000.0 AS PriorOtherInThousands FROM Ranked r WHERE @SortBy = 'Alphabetical' OR @SortBy = 'TopAll' OR (@SortBy = 'Top25' AND r.ClientRank <= 25) OR (@SortBy = 'Top50' AND r.ClientRank <= 50) OR (@SortBy = 'Top100' AND r.ClientRank <= 100) ORDER BY CASE WHEN @SortBy = 'Alphabetical' THEN r.FormalName END, CASE WHEN @SortBy = 'Alphabetical' THEN r.CorpCode END, r.ClientRank;