/* Ranked Client Revenue — Service Detail ====================================== BO: frmRankedClientRevenue (OptServiceDetail) SP: RAC.dbo.RELO_RankClientRevSerVice Crystal: ClientRevenuebyServiceTop25/50/100/All/Alpha.rpt Run on RAC. Profit center / CAC filter omitted. Service Detail is current-period only (fraRevYear disabled on the form). Rank-by column options are disabled for this mode; SortBy still applies using total of the detail service amounts for Top N. Form fields are returned in the final SELECT for filtering / grouping. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @ReportType varchar(10) = 'Regular'; -- Regular | CRD DECLARE @MarketingCRDFilter varchar(10) = NULL; -- see Service Groups script DECLARE @ParentRollup bit = 0; DECLARE @IncludeDirectCosts bit = 0; DECLARE @GroupBy varchar(20) = 'ServiceDetail'; DECLARE @SortBy varchar(20) = 'Top100'; -- Alphabetical | Top25 | Top50 | Top100 | TopAll DECLARE @RankByYear varchar(10) = 'Current'; -- form disabled; kept for select list DECLARE @RankByColumn varchar(20) = 'Total'; -- form disabled; kept for select list IF OBJECT_ID('tempdb..#RevLines') IS NOT NULL DROP TABLE #RevLines; CREATE TABLE #RevLines ( CorpCode varchar(15) NOT NULL, DocDate datetime NULL, Amount numeric(19, 5) NOT NULL, ServiceCode varchar(4) NULL, ServiceGroupCode varchar(4) NULL, Source varchar(10) NOT NULL ); /* ---------- 1) SOP ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(h.DOCDATE), SUM(l.XTNDPRCE), w.ServiceCode, MAX(s.ServiceGroupCode), 'SOP' FROM dbo.SOP30200 h INNER JOIN dbo.SOP30300 l ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE INNER JOIN dbo.IV00101 i ON l.ITEMNMBR = i.ITEMNMBR INNER JOIN dbo.GL00100 g ON i.IVSLSIDX = g.ACTINDX INNER JOIN dbo.mdSOPLineWORK w ON l.SOPNUMBE = w.SOPNUMBE AND l.SOPTYPE = w.SOPTYPE AND l.LNITMSEQ = w.LNITMSEQ AND l.CMPNTSEQ = w.CMPNTSEQ INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE h.VOIDSTTS <> 1 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND ( LEFT(l.ITEMNMBR, 1) = 'F' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND h.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, h.DOCDATE; /* ---------- 2) GL ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), w.ServiceCode, MAX(s.ServiceGroupCode), 'GL' FROM dbo.GL20000 gl INNER JOIN dbo.GL00100 g ON gl.ACTINDX = g.ACTINDX INNER JOIN dbo.mdGLTrxLineWORK w ON gl.JRNENTRY = w.JRNENTRY AND gl.SEQNUMBR = w.SQNCLINE INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND (gl.DEBITAMT - gl.CRDTAMNT) <> 0 AND ( LEFT(w.CODEID, 3) = 'FEE' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND gl.TRXDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, gl.TRXDATE UNION ALL SELECT m.CorpCode, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), w.ServiceCode, MAX(s.ServiceGroupCode), 'GL' FROM dbo.GL30000 gl INNER JOIN dbo.GL00100 g ON gl.ACTINDX = g.ACTINDX INNER JOIN dbo.mdGLTrxLineWORK w ON gl.JRNENTRY = w.JRNENTRY AND gl.SEQNUMBR = w.SQNCLINE INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND (gl.DEBITAMT - gl.CRDTAMNT) <> 0 AND ( LEFT(w.CODEID, 3) = 'FEE' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND gl.TRXDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, gl.TRXDATE; /* ---------- 3) Credit memos ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), MAX(w.ServiceCode), MAX(s.ServiceGroupCode), 'RM' FROM dbo.RM10101 d INNER JOIN dbo.RM20101 rm ON d.DOCNUMBR = rm.DOCNUMBR INNER JOIN dbo.GL00100 g ON d.DSTINDX = g.ACTINDX INNER JOIN dbo.mdRMDistWORK w ON d.DOCNUMBR = w.DOCNUMBR AND d.SEQNUMBR = w.SEQNUMBR AND d.USERID = w.USERID AND d.RMDTYPAL = w.RMDTYPAL INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE rm.RMDTYPAL = 7 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND d.DEBITAMT <> 0 AND ( LEFT(w.CODEID, 3) = 'FEE' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND rm.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, rm.DOCDATE UNION ALL SELECT m.CorpCode, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), MAX(w.ServiceCode), MAX(s.ServiceGroupCode), 'RM' FROM dbo.RM30301 d INNER JOIN dbo.RM30101 rm ON d.DOCNUMBR = rm.DOCNUMBR INNER JOIN dbo.GL00100 g ON d.DSTINDX = g.ACTINDX INNER JOIN dbo.mdRMDistWORK w ON d.DOCNUMBR = w.DOCNUMBR AND d.SEQNUMBR = w.SEQNUMBR AND d.USERID = w.USERID AND d.RMDTYPAL = w.RMDTYPAL INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE rm.RMDTYPAL = 7 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND d.DEBITAMT <> 0 AND ( LEFT(w.CODEID, 3) = 'FEE' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND rm.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, rm.DOCDATE; /* ---------- 4) Voids ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceGroupCode, Source) SELECT MAX(m.CorpCode), MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), w.ServiceCode, MAX(s.ServiceGroupCode), 'VOID' FROM dbo.RM20101 rm INNER JOIN dbo.SOP30200 h ON rm.DOCNUMBR = h.SOPNUMBE INNER JOIN dbo.SOP30300 l ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE INNER JOIN dbo.IV00101 i ON l.ITEMNMBR = i.ITEMNMBR INNER JOIN dbo.GL00100 g ON i.IVSLSIDX = g.ACTINDX INNER JOIN dbo.mdSOPLineWORK w ON l.SOPNUMBE = w.SOPNUMBE AND l.SOPTYPE = w.SOPTYPE AND l.LNITMSEQ = w.LNITMSEQ AND l.CMPNTSEQ = w.CMPNTSEQ INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE rm.VOIDSTTS = 1 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND ( LEFT(l.ITEMNMBR, 1) = 'F' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND rm.VOIDDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, rm.VOIDDATE UNION ALL SELECT MAX(m.CorpCode), MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), w.ServiceCode, MAX(s.ServiceGroupCode), 'VOID' FROM dbo.RM30101 rm INNER JOIN dbo.SOP30200 h ON rm.DOCNUMBR = h.SOPNUMBE INNER JOIN dbo.SOP30300 l ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE INNER JOIN dbo.IV00101 i ON l.ITEMNMBR = i.ITEMNMBR INNER JOIN dbo.GL00100 g ON i.IVSLSIDX = g.ACTINDX INNER JOIN dbo.mdSOPLineWORK w ON l.SOPNUMBE = w.SOPNUMBE AND l.SOPTYPE = w.SOPTYPE AND l.LNITMSEQ = w.LNITMSEQ AND l.CMPNTSEQ = w.CMPNTSEQ INNER JOIN dbo.tblRAServices s ON w.ServiceCode = s.ServiceCode INNER JOIN dbo.tblTEMain1 m ON w.GROUPID = m.TEFileNo WHERE rm.VOIDSTTS = 1 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND ( LEFT(l.ITEMNMBR, 1) = 'F' OR (@IncludeDirectCosts = 1 AND SUBSTRING(w.CODEID, 5, 3) = '777') ) AND rm.VOIDDATE BETWEEN @StartDate AND @EndDate GROUP BY m.CorpCode, w.ServiceCode, rm.VOIDDATE; /* Aggregate to client + service group + service code (SP grain before pivot) */ IF OBJECT_ID('tempdb..#Agg') IS NOT NULL DROP TABLE #Agg; SELECT RTRIM(CorpCode) AS CorpCode, ServiceGroupCode, ServiceCode, SUM(Amount) AS Amount INTO #Agg FROM #RevLines WHERE RTRIM(ISNULL(CorpCode, '')) <> '' AND DocDate BETWEEN @StartDate AND @EndDate GROUP BY RTRIM(CorpCode), ServiceGroupCode, ServiceCode; /* Pivot service codes the same way as RELO_RankClientRevSerVice */ IF OBJECT_ID('tempdb..#ByClientGroup') IS NOT NULL DROP TABLE #ByClientGroup; 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 INTO #ByClientGroup FROM #Agg GROUP BY CorpCode, ServiceGroupCode; /* Parent rollup */ IF OBJECT_ID('tempdb..#Rolled') IS NOT NULL DROP TABLE #Rolled; 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 INTO #Rolled FROM #ByClientGroup b LEFT JOIN dbo.tblCorpClient c ON RTRIM(c.CorpCode) = RTRIM(b.CorpCode) WHERE b.ServiceGroupCode IN ('ADMN', 'DEST', 'DEPT', 'INTL') -- SP final WHERE GROUP BY CASE WHEN @ParentRollup = 1 THEN ISNULL(NULLIF(RTRIM(c.ParentCompany), ''), RTRIM(b.CorpCode)) ELSE RTRIM(b.CorpCode) END, b.ServiceGroupCode; ;WITH 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.tblCorpClient cc ON RTRIM(cc.CorpCode) = RTRIM(r.CorpCode) LEFT JOIN dbo.tblInitials 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)) ) ), /* Rank clients by total across their service-group rows */ 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;