/* Ranked Client Revenue — Service Groups ====================================== BO: frmRankedClientRevenue (OptServiceGroups) SP: RAC.dbo.RELO_RankedClientRevenue Crystal: RankedClientRevenueTop100.rpt RankedClientRevenueTopAll.rpt RankedClientRevenueAlpha.rpt (also Top25 / Top50 when those .rpt files exist) Run on RAC (same as the BO stored procedure). Profit center / CAC filter is intentionally omitted (no ACTNUMBR_2 filter). Form fields are returned in the final SELECT for filtering / grouping: StartDate, EndDate, ReportType, MarketingCRDFilter, ParentRollup, IncludeDirectCosts, GroupBy, SortBy, RankByYear, RankByColumn, CorpCode, ParentCompany, FormalName, MarketingCRD, CRDLastName, CRDFirstName, RankAmount, ClientRank, + current/prior service-group amounts. Crystal displays amounts / 1000 (BO loads tblTmpClientRanking that way). *InThousands columns match Crystal; raw amounts are also returned. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @ReportType varchar(10) = 'Regular'; -- Regular | CRD DECLARE @MarketingCRDFilter varchar(10) = NULL; -- CRD mode only: -- NULL = All CRD Clients (has MarketingCRD) -- '' = All Unassigned Clients (no MarketingCRD) -- 'ABC' = specific CRD initials DECLARE @ParentRollup bit = 0; -- chkParentRollup DECLARE @IncludeDirectCosts bit = 0; -- chkIncludeDirectCosts (CODEID mid = 777) DECLARE @GroupBy varchar(20) = 'ServiceGroups'; -- form: Service Groups 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); 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, ServiceType numeric(1, 0) NULL, ServiceGroupCode varchar(4) NULL, Source varchar(10) NOT NULL ); /* ---------- 1) SOP invoices (non-void) ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceType, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(h.DOCDATE), SUM(l.XTNDPRCE), w.ServiceCode, w.ServiceType, 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) OR (h.DOCDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, h.DOCDATE; /* ---------- 2) GL open + history ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceType, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), w.ServiceCode, w.ServiceType, 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) OR (gl.TRXDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, gl.TRXDATE UNION ALL SELECT m.CorpCode, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), w.ServiceCode, w.ServiceType, 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) OR (gl.TRXDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, gl.TRXDATE; /* ---------- 3) Credit memos (RM type 7) ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceType, ServiceGroupCode, Source) SELECT m.CorpCode, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), w.ServiceCode, w.ServiceType, 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) OR (rm.DOCDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, rm.DOCDATE UNION ALL SELECT m.CorpCode, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), w.ServiceCode, w.ServiceType, 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) OR (rm.DOCDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, rm.DOCDATE; /* ---------- 4) SOP voids (negate on VOIDDATE) ---------- */ INSERT INTO #RevLines (CorpCode, DocDate, Amount, ServiceCode, ServiceType, ServiceGroupCode, Source) SELECT MAX(m.CorpCode), MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), w.ServiceCode, w.ServiceType, 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) OR (rm.VOIDDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, rm.VOIDDATE UNION ALL SELECT MAX(m.CorpCode), MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), w.ServiceCode, w.ServiceType, 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) OR (rm.VOIDDATE BETWEEN @PriorStart AND @PriorEnd) ) GROUP BY m.CorpCode, s.ServiceGroupCode, w.ServiceCode, w.ServiceType, rm.VOIDDATE; /* BO rule: ServiceType = 2 (destination) rolls into INTL service group */ UPDATE #RevLines SET ServiceGroupCode = 'INTL' WHERE ServiceType = 2; /* ---------- Pivot current / prior by service group ---------- */ IF OBJECT_ID('tempdb..#ByClient') IS NOT NULL DROP TABLE #ByClient; SELECT RTRIM(CorpCode) AS CorpCode, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS CurrDept, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'DEPT' THEN Amount ELSE 0 END) AS PriorDept, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS CurrDest, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'DEST' THEN Amount ELSE 0 END) AS PriorDest, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'INTL' THEN Amount ELSE 0 END) AS CurrIntl, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'INTL' THEN Amount ELSE 0 END) AS PriorIntl, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS CurrAdmn, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'ADMN' THEN Amount ELSE 0 END) AS PriorAdmn, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode = 'MORT' THEN Amount ELSE 0 END) AS CurrMort, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode = 'MORT' THEN Amount ELSE 0 END) AS PriorMort, SUM(CASE WHEN DocDate BETWEEN @StartDate AND @EndDate AND ServiceGroupCode NOT IN ('DEPT', 'DEST', 'INTL', 'ADMN', 'MORT') THEN Amount ELSE 0 END) AS CurrOther, SUM(CASE WHEN DocDate BETWEEN @PriorStart AND @PriorEnd AND ServiceGroupCode NOT IN ('DEPT', 'DEST', 'INTL', 'ADMN', 'MORT') THEN Amount ELSE 0 END) AS PriorOther INTO #ByClient FROM #RevLines WHERE RTRIM(ISNULL(CorpCode, '')) <> '' GROUP BY RTRIM(CorpCode); /* ---------- Parent rollup + client / CRD attributes ---------- */ 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, 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 INTO #Rolled FROM #ByClient b LEFT JOIN dbo.tblCorpClient 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; /* ---------- Final ranked result ---------- */ ;WITH 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 /* Total — BO total excludes Other */ 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.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)) ) ), 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 /* Form / filter / group fields */ @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, /* Client */ r.CorpCode, r.ParentCompany, r.FormalName, r.MarketingCRD, r.CRDLastName, r.CRDFirstName, r.ClientRank, r.RankAmount, r.RankAmount / 1000.0 AS RankAmountInThousands, /* Current period (SP: CXTNDPRCE*) */ 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, /* Prior period (SP: PXTNDPRCE*) */ 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;