--01 Ranked Client Revenue Service Groups.sql -- SE rewrite of BO Ranked Client Revenue Service Groups (FactRankedClientRevenue) -- View: udv_transaction_detail_34 -- Note: ServiceGroupCode (DEPT/DEST/INTL/ADMN/MORT), ParentCompany, MarketingCRD, IsDirectCost -- are not in SEViews. Curr*/Prior* group pivots left NULL; RankAmount = total txn amount. DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @ReportType varchar(10) = 'Regular'; DECLARE @MarketingCRDFilter varchar(10) = NULL; -- unable to find DECLARE @ParentRollup bit = 0; -- ParentCompany — NOT USED IN SE DECLARE @IncludeDirectCosts bit = 0; -- IsDirectCost — unable to find DECLARE @GroupBy varchar(20) = 'ServiceGroups'; DECLARE @SortBy varchar(20) = 'Top100'; DECLARE @RankByYear varchar(10) = 'Current'; DECLARE @RankByColumn varchar(20) = 'Total'; DECLARE @PriorStart datetime = DATEADD(YEAR, -1, @StartDate); DECLARE @PriorEnd datetime = DATEADD(YEAR, -1, @EndDate); ;WITH ByClient AS ( SELECT td.[Corp Code] AS CorpCode, MAX(td.[Org Name]) AS FormalName, SUM(CASE WHEN td.[Transaction Date] BETWEEN @StartDate AND @EndDate THEN ISNULL(td.[Transaction Amount], 0) ELSE 0 END) AS CurrTotal, SUM(CASE WHEN td.[Transaction Date] BETWEEN @PriorStart AND @PriorEnd THEN ISNULL(td.[Transaction Amount], 0) ELSE 0 END) AS PriorTotal FROM se_reports.dbo.[udv_transaction_detail_34] td WHERE td.[Transaction Date] IS NOT NULL AND ( (td.[Transaction Date] BETWEEN @StartDate AND @EndDate) OR (td.[Transaction Date] BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY td.[Corp Code] ), Ranked AS ( 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, b.CorpCode, NULL AS ParentCompany, -- NOT USED IN SE b.FormalName, NULL AS MarketingCRD, -- unable to find NULL AS CRDLastName, -- unable to find NULL AS CRDFirstName, -- unable to find ROW_NUMBER() OVER ( ORDER BY CASE WHEN @RankByYear = 'Prior' THEN b.PriorTotal ELSE b.CurrTotal END DESC ) AS ClientRank, CASE WHEN @RankByYear = 'Prior' THEN b.PriorTotal ELSE b.CurrTotal END AS RankAmount, CASE WHEN @RankByYear = 'Prior' THEN b.PriorTotal ELSE b.CurrTotal END / 1000.0 AS RankAmountInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrDept, -- ServiceGroupCode — unable to find CAST(NULL AS DECIMAL(18, 2)) AS CurrDest, CAST(NULL AS DECIMAL(18, 2)) AS CurrIntl, CAST(NULL AS DECIMAL(18, 2)) AS CurrAdmn, CAST(NULL AS DECIMAL(18, 2)) AS CurrMort, CAST(NULL AS DECIMAL(18, 2)) AS CurrOther, CAST(NULL AS DECIMAL(18, 2)) AS CurrDeptInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrDestInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrIntlInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrAdmnInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrMortInThousands, CAST(NULL AS DECIMAL(18, 2)) AS CurrOtherInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorDept, CAST(NULL AS DECIMAL(18, 2)) AS PriorDest, CAST(NULL AS DECIMAL(18, 2)) AS PriorIntl, CAST(NULL AS DECIMAL(18, 2)) AS PriorAdmn, CAST(NULL AS DECIMAL(18, 2)) AS PriorMort, CAST(NULL AS DECIMAL(18, 2)) AS PriorOther, CAST(NULL AS DECIMAL(18, 2)) AS PriorDeptInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorDestInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorIntlInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorAdmnInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorMortInThousands, CAST(NULL AS DECIMAL(18, 2)) AS PriorOtherInThousands, b.CurrTotal, b.PriorTotal FROM ByClient b ) SELECT * FROM Ranked WHERE ( @SortBy = 'TopAll' OR @SortBy = 'Alphabetical' OR (@SortBy = 'Top25' AND ClientRank <= 25) OR (@SortBy = 'Top50' AND ClientRank <= 50) OR (@SortBy = 'Top100' AND ClientRank <= 100) ) ORDER BY CASE WHEN @SortBy = 'Alphabetical' THEN FormalName END, ClientRank;