--02 Ranked Client Revenue Service Detail.sql -- SE rewrite of BO Ranked Client Revenue Service Detail -- View: udv_transaction_detail_34 -- Note: BO pivots by ServiceCode; SE exposes Service name only. DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @ReportType varchar(10) = 'Regular'; DECLARE @MarketingCRDFilter varchar(10) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @IncludeDirectCosts bit = 0; DECLARE @SortBy varchar(20) = 'Top100'; DECLARE @RankByYear varchar(10) = 'Current'; DECLARE @PriorStart datetime = DATEADD(YEAR, -1, @StartDate); DECLARE @PriorEnd datetime = DATEADD(YEAR, -1, @EndDate); ;WITH Rev AS ( SELECT td.[Corp Code] AS CorpCode, MAX(td.[Org Name]) AS FormalName, td.[Service] AS ServiceName, SUM(CASE WHEN td.[Transaction Date] BETWEEN @StartDate AND @EndDate THEN ISNULL(td.[Transaction Amount], 0) ELSE 0 END) AS CurrAmount, SUM(CASE WHEN td.[Transaction Date] BETWEEN @PriorStart AND @PriorEnd THEN ISNULL(td.[Transaction Amount], 0) ELSE 0 END) AS PriorAmount 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], td.[Service] ), ClientTotals AS ( SELECT CorpCode, MAX(FormalName) AS FormalName, SUM(CurrAmount) AS CurrTotal, SUM(PriorAmount) AS PriorTotal FROM Rev GROUP BY CorpCode ), RankedClients AS ( SELECT CorpCode, FormalName, CurrTotal, PriorTotal, ROW_NUMBER() OVER ( ORDER BY CASE WHEN @RankByYear = 'Prior' THEN PriorTotal ELSE CurrTotal END DESC ) AS ClientRank FROM ClientTotals ) SELECT @StartDate AS StartDate, @EndDate AS EndDate, @ReportType AS ReportType, @MarketingCRDFilter AS MarketingCRDFilter, @ParentRollup AS ParentRollup, @IncludeDirectCosts AS IncludeDirectCosts, @SortBy AS SortBy, @RankByYear AS RankByYear, r.CorpCode, NULL AS ParentCompany, -- NOT USED IN SE rc.FormalName, NULL AS MarketingCRD, -- unable to find rc.ClientRank, CASE WHEN @RankByYear = 'Prior' THEN rc.PriorTotal ELSE rc.CurrTotal END AS RankAmount, r.ServiceName AS ServiceCode, -- description, not BO service code NULL AS ServiceGroupCode, -- unable to find r.CurrAmount, r.PriorAmount FROM Rev r JOIN RankedClients rc ON rc.CorpCode = r.CorpCode WHERE ( @SortBy = 'TopAll' OR @SortBy = 'Alphabetical' OR (@SortBy = 'Top25' AND rc.ClientRank <= 25) OR (@SortBy = 'Top50' AND rc.ClientRank <= 50) OR (@SortBy = 'Top100' AND rc.ClientRank <= 100) ) ORDER BY rc.ClientRank, r.ServiceName;