/* Ranked Client Revenue — Service Detail (DatawarehouseNCM) ========================================================= BO: frmRankedClientRevenue (OptServiceDetail) SP: RELO_RankClientRevSerVice Fact: dbo.FactRankedClientRevenue Current period only. Run on DatawarehouseNCM after populate. */ 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 @GroupBy varchar(20) = 'ServiceDetail'; DECLARE @SortBy varchar(20) = 'Top100'; DECLARE @RankByYear varchar(10) = 'Current'; DECLARE @RankByColumn varchar(20) = 'Total'; ;WITH Rev AS ( SELECT RTRIM(f.CorpCode) AS CorpCode, f.ServiceGroupCode, f.ServiceCode, SUM(f.Amount) AS Amount FROM dbo.FactRankedClientRevenue f WHERE f.TrxDate BETWEEN @StartDate AND @EndDate AND ( @IncludeDirectCosts = 1 OR ISNULL(f.IsDirectCost, 0) = 0 ) AND RTRIM(ISNULL(f.CorpCode, '')) <> '' GROUP BY RTRIM(f.CorpCode), f.ServiceGroupCode, f.ServiceCode ), Pivoted AS ( SELECT CorpCode, ServiceGroupCode, SUM(CASE WHEN ServiceCode = 'HBO' AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS HBODept, SUM(CASE WHEN ServiceCode = 'MAP' AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS MAPDept, SUM(CASE WHEN ServiceCode = 'DOS' AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS DOSDept, SUM(CASE WHEN ServiceCode = 'MVP' AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS MVPDept, SUM(CASE WHEN ServiceGroupCode = 'DEPT' AND ServiceCode NOT IN ('HBO', 'MAP', 'DOS', 'MVP') THEN Amount ELSE 0 END) AS OtherDept, SUM(CASE WHEN ServiceCode = 'ACR' AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS ACRDest, SUM(CASE WHEN ServiceCode = 'ACP' AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS ACPDest, SUM(CASE WHEN ServiceCode = 'ACG' AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS ACGDest, SUM(CASE WHEN ServiceCode = 'NHF' AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS NHFDest, SUM(CASE WHEN ServiceGroupCode = 'DEST' AND ServiceCode NOT IN ('ACR', 'ACG', 'ACP', 'NHF') THEN Amount ELSE 0 END) AS OtherDest, SUM(CASE WHEN ServiceCode = 'CRP' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS CRPAdmn, SUM(CASE WHEN ServiceCode IN ('ERP', 'LSP') THEN Amount ELSE 0 END) AS ERPAdmn, SUM(CASE WHEN ServiceCode IN ('HHG', 'HHA', 'HGR') THEN Amount ELSE 0 END) AS HHGAdmn, SUM(CASE WHEN ServiceCode = 'POL' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS POLAdmn, SUM(CASE WHEN ServiceCode IN ('TLV', 'TLR') THEN Amount ELSE 0 END) AS TLVAdmn, SUM(CASE WHEN ServiceCode = 'CRS' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS CRSAdmn, SUM(CASE WHEN ServiceCode = 'YET' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS YETAdmn, SUM(CASE WHEN ServiceCode = 'RTS' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS RTSAdmn, SUM(CASE WHEN ServiceCode = 'PSV' AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS PSVAdmn, SUM(CASE WHEN ServiceGroupCode = 'ADMN' AND ServiceCode NOT IN ('CRP', 'ERP', 'HHG', 'POL', 'TLV', 'CRS', 'YET', 'RTS', 'PSV', 'LSP', 'HHA', 'HGR', 'TLR') THEN Amount ELSE 0 END) AS OtherAdmn, SUM(CASE WHEN ServiceCode = 'ACI' AND ServiceGroupCode = 'INTL' THEN Amount ELSE 0 END) AS ACIIntl, SUM(CASE WHEN ServiceGroupCode = 'INTL' AND ServiceCode <> 'ACI' THEN Amount ELSE 0 END) AS OtherIntl FROM Rev GROUP BY CorpCode, ServiceGroupCode ), Rolled AS ( SELECT CASE WHEN @ParentRollup = 1 THEN ISNULL(NULLIF(RTRIM(c.ParentCompany), ''), RTRIM(b.CorpCode)) ELSE RTRIM(b.CorpCode) END AS CorpCode, b.ServiceGroupCode, MAX(RTRIM(c.ParentCompany)) AS ParentCompany, SUM(b.HBODept) AS HBODept, SUM(b.MAPDept) AS MAPDept, SUM(b.DOSDept) AS DOSDept, SUM(b.MVPDept) AS MVPDept, SUM(b.OtherDept) AS OtherDept, SUM(b.ACRDest) AS ACRDest, SUM(b.ACPDest) AS ACPDest, SUM(b.ACGDest) AS ACGDest, SUM(b.NHFDest) AS NHFDest, SUM(b.OtherDest) AS OtherDest, SUM(b.CRPAdmn) AS CRPAdmn, SUM(b.ERPAdmn) AS ERPAdmn, SUM(b.HHGAdmn) AS HHGAdmn, SUM(b.POLAdmn) AS POLAdmn, SUM(b.TLVAdmn) AS TLVAdmn, SUM(b.CRSAdmn) AS CRSAdmn, SUM(b.YETAdmn) AS YETAdmn, SUM(b.RTSAdmn) AS RTSAdmn, SUM(b.PSVAdmn) AS PSVAdmn, SUM(b.OtherAdmn) AS OtherAdmn, SUM(b.ACIIntl) AS ACIIntl, SUM(b.OtherIntl) AS OtherIntl FROM Pivoted b LEFT JOIN dbo.DimCorpClient c ON RTRIM(c.CorpCode) = RTRIM(b.CorpCode) WHERE b.ServiceGroupCode IN ('ADMN', 'DEST', 'DEPT', 'INTL') GROUP BY CASE WHEN @ParentRollup = 1 THEN ISNULL(NULLIF(RTRIM(c.ParentCompany), ''), RTRIM(b.CorpCode)) ELSE RTRIM(b.CorpCode) END, b.ServiceGroupCode ), WithAttrs AS ( SELECT r.*, cc.FormalName, cc.MarketingCRD, i.LastName AS CRDLastName, i.FirstName AS CRDFirstName, ( r.HBODept + r.MAPDept + r.DOSDept + r.MVPDept + r.OtherDept + r.ACRDest + r.ACPDest + r.ACGDest + r.NHFDest + r.OtherDest + r.CRPAdmn + r.ERPAdmn + r.HHGAdmn + r.POLAdmn + r.TLVAdmn + r.CRSAdmn + r.YETAdmn + r.RTSAdmn + r.PSVAdmn + r.OtherAdmn + r.ACIIntl + r.OtherIntl ) 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)) ) ), ClientTotals AS ( SELECT CorpCode, SUM(RankAmount) AS ClientTotal FROM Filtered GROUP BY CorpCode ), ClientRanked AS ( SELECT ct.CorpCode, ct.ClientTotal, CASE WHEN @SortBy = 'Alphabetical' THEN NULL ELSE ROW_NUMBER() OVER (ORDER BY ct.ClientTotal DESC, ct.CorpCode) END AS ClientRank FROM ClientTotals ct ) 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, f.CorpCode, f.ParentCompany, f.FormalName, f.MarketingCRD, f.CRDLastName, f.CRDFirstName, f.ServiceGroupCode, cr.ClientRank, cr.ClientTotal AS RankAmount, f.HBODept, f.MAPDept, f.DOSDept, f.MVPDept, f.OtherDept, f.ACRDest, f.ACPDest, f.ACGDest, f.NHFDest, f.OtherDest, f.CRPAdmn, f.ERPAdmn, f.HHGAdmn, f.POLAdmn, f.TLVAdmn, f.CRSAdmn, f.YETAdmn, f.RTSAdmn, f.PSVAdmn, f.OtherAdmn, f.ACIIntl, f.OtherIntl FROM Filtered f INNER JOIN ClientRanked cr ON cr.CorpCode = f.CorpCode WHERE @SortBy = 'Alphabetical' OR @SortBy = 'TopAll' OR (@SortBy = 'Top25' AND cr.ClientRank <= 25) OR (@SortBy = 'Top50' AND cr.ClientRank <= 50) OR (@SortBy = 'Top100' AND cr.ClientRank <= 100) ORDER BY CASE WHEN @SortBy = 'Alphabetical' THEN f.FormalName END, CASE WHEN @SortBy = 'Alphabetical' THEN f.CorpCode END, cr.ClientRank, f.ServiceGroupCode;