/* Revenue By State ================ PBI report: "Revenue By state" (Rules/revenueBystatePBIScreenshot.png) DAX source: Rules/daxRevByState.txt (table RevenueData) SP source: RAC.dbo.RELO_ClientRevDetail_State_PBI Crystal: "Revenue by State OLD.rpt" (older; PBI uses the _PBI SP) Run on RAC. PBI columns (from DAX / screenshot): ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, GROUPID, TELastName, DOCDATE, CODEID, XTNDPRCE, City, State Filters: DOCDATE between @StartDate and @EndDate optional @State (PBI "Select State"; NULL = All) State / City rule (from SP): If ACTNUMBR_3 in (MVP, HBO, MAP) use departure work state/city Else use destination work state/city Special: FEE-310 referrals come from DOTA payment views as ACTNUMBR_1 = 7020 (other GL 7020 FEE lines are excluded to avoid double-count). */ DECLARE @StartDate datetime = '2025-03-02'; DECLARE @EndDate datetime = '2026-05-14'; -- PBI slicer end (inclusive) DECLARE @State varchar(50) = NULL; -- NULL = All; e.g. 'California' /* ---------- DOTA FEE-310 referrals (replaces GL 7020 for this report) ---------- */ DECLARE @tblTmpFRRptData TABLE ( TEFileNo varchar(15), PhsCtg varchar(7), Amount money, TrxDate datetime, ServiceCode varchar(5), ServiceType smallint, InOut varchar(1), Source char(3), DocumentNo varchar(21), CustVendNo varchar(15), VoidStatus smallint, CashType varchar(7), GLDescription varchar(31), GLJrnEntry int, VendorDocument varchar(21), GPDatabase varchar(50) ); INSERT INTO @tblTmpFRRptData ( TEFileNo, PhsCtg, Amount, TrxDate, ServiceCode, ServiceType, InOut, Source, DocumentNo, CustVendNo, VoidStatus, CashType, GLDescription, GLJrnEntry, VendorDocument, GPDatabase ) SELECT p.GROUPID, p.CODEID, -p.DEBITAMT, p.TrxDate, p.ServiceCode, p.ServiceType, 'O', 'APP', p.DocumentNo, p.CustVendNo, 0, 'CASH', '', 0, p.APTODCNM, 'RAC' FROM dbo.vwDOTAPaymentsOps p INNER JOIN dbo.tblTEMain1 m ON p.GROUPID = m.TEFileNo WHERE p.DEBITAMT <> 0 AND p.TrxDate >= @StartDate AND p.TrxDate <= @EndDate AND p.CODEID = 'FEE-310'; INSERT INTO @tblTmpFRRptData ( TEFileNo, PhsCtg, Amount, TrxDate, ServiceCode, ServiceType, InOut, Source, DocumentNo, CustVendNo, VoidStatus, CashType, GLDescription, GLJrnEntry, VendorDocument, GPDatabase ) SELECT p.GROUPID, p.CODEID, -p.CREDITAMT, p.TrxDate, p.ServiceCode, p.ServiceType, 'O', 'DM', p.DMVOUCHER, p.CustVendNo, 0, 'CASH', '', 0, p.APTODCNM, 'RAC' FROM dbo.vwDOTAPaymentsOps p INNER JOIN dbo.tblTEMain1 m ON p.GROUPID = m.TEFileNo WHERE p.CREDITAMT > 0 AND p.TrxDate >= @StartDate AND p.TrxDate <= @EndDate AND p.CODEID = 'FEE-310'; INSERT INTO @tblTmpFRRptData ( TEFileNo, PhsCtg, Amount, TrxDate, ServiceCode, ServiceType, InOut, Source, DocumentNo, CustVendNo, VoidStatus, CashType, GLDescription, GLJrnEntry, VendorDocument, GPDatabase ) SELECT d.GROUPID, d.CODEID, -d.DEBITAMT, d.TrxDate, d.ServiceCode, d.ServiceType, 'O', 'APP', d.DocumentNo, d.CustVendNo, 0, 'CASH', '', 0, '', 'RAC' FROM dbo.vwDOTADebitMemos d INNER JOIN dbo.tblTEMain1 m ON d.GROUPID = m.TEFileNo WHERE d.DEBITAMT < 0 AND d.TrxDate >= @StartDate AND d.TrxDate <= @EndDate AND d.CODEID = 'FEE-310'; INSERT INTO @tblTmpFRRptData ( TEFileNo, PhsCtg, Amount, TrxDate, ServiceCode, ServiceType, InOut, Source, DocumentNo, CustVendNo, VoidStatus, CashType, GLDescription, GLJrnEntry, VendorDocument, GPDatabase ) SELECT d.GROUPID, d.CODEID, -d.CRDTAMNT, d.TrxDate, d.ServiceCode, d.ServiceType, 'O', 'DM', d.DocumentNo, d.CustVendNo, 0, 'CASH', '', 0, '', 'RAC' FROM dbo.vwDOTADebitMemos d INNER JOIN dbo.tblTEMain1 m ON d.GROUPID = m.TEFileNo WHERE d.CRDTAMNT > 0 AND d.TrxDate >= @StartDate AND d.TrxDate <= @EndDate AND d.CODEID = 'FEE-310'; INSERT INTO @tblTmpFRRptData ( TEFileNo, PhsCtg, Amount, TrxDate, ServiceCode, ServiceType, InOut, Source, DocumentNo, CustVendNo, VoidStatus, CashType, GLDescription, GLJrnEntry, VendorDocument, GPDatabase ) SELECT p.GROUPID, p.CODEID, -p.DEBITAMT, p.TrxDate, p.ServiceCode, p.ServiceType, 'O', 'APP', p.DocumentNo, p.CustVendNo, 0, 'CASH', '', 0, '', 'RAC' FROM dbo.vwDOTAPaymentsByDM p INNER JOIN dbo.tblTEMain1 m ON p.GROUPID = m.TEFileNo WHERE p.TrxDate >= @StartDate AND p.TrxDate <= @EndDate AND p.CODEID = 'FEE-310'; /* ---------- Revenue detail lines ---------- */ DECLARE @TMP_Rev TABLE ( CUSTNMBR char(15), GROUPID char(15), DOCDATE datetime, XTNDPRCE numeric(19, 5), ACTDESCR char(51), ACTNUMBR_1 char(5), ACTNUMBR_2 char(5), ACTNUMBR_3 char(5), ACTNUMBR_4 char(5), CODEID char(7) ); -- 1) SOP INSERT INTO @TMP_Rev SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(h.DOCDATE), SUM(l.XTNDPRCE), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE LEFT(l.ITEMNMBR, 1) = 'F' AND h.VOIDSTTS <> 1 AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND h.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, g.ACTDESCR, h.DOCDATE, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID; -- 2) DOTA FEE-310 referrals as 7020 INSERT INTO @TMP_Rev SELECT b.CorpCode, a.TEFileNo, a.TrxDate, SUM(a.Amount), 'Referrals', '7020', '', a.ServiceCode, CASE WHEN a.ServiceType = 1 THEN 'D' ELSE 'I' END, a.PhsCtg FROM @tblTmpFRRptData a INNER JOIN dbo.tblTEMain1 b ON a.TEFileNo = b.TEFileNo WHERE a.VoidStatus = 0 GROUP BY b.CorpCode, a.TEFileNo, a.TrxDate, a.ServiceCode, a.ServiceType, a.PhsCtg; -- 3) GL (exclude 7020 — covered by DOTA above) INSERT INTO @TMP_Rev SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND LEFT(w.CODEID, 3) = 'FEE' AND (gl.DEBITAMT - gl.CRDTAMNT) <> 0 AND g.ACTNUMBR_1 <> '7020' AND gl.TRXDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, gl.TRXDATE, g.ACTDESCR, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID UNION ALL SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(gl.TRXDATE), SUM(-(gl.DEBITAMT - gl.CRDTAMNT)), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND LEFT(w.CODEID, 3) = 'FEE' AND (gl.DEBITAMT - gl.CRDTAMNT) <> 0 AND g.ACTNUMBR_1 <> '7020' AND gl.TRXDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, gl.TRXDATE, g.ACTDESCR, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID; -- 4) Credit memos INSERT INTO @TMP_Rev SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND rm.RMDTYPAL = 7 AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND LEFT(w.CODEID, 3) = 'FEE' AND d.DEBITAMT <> 0 AND rm.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, g.ACTDESCR, rm.DOCDATE, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID UNION ALL SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(rm.DOCDATE), -SUM(d.DEBITAMT), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE LEFT(g.ACTNUMBR_1, 1) = '7' AND rm.RMDTYPAL = 7 AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND LEFT(w.CODEID, 3) = 'FEE' AND d.DEBITAMT <> 0 AND rm.DOCDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, g.ACTDESCR, rm.DOCDATE, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID; -- 5) Voids INSERT INTO @TMP_Rev SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE rm.VOIDSTTS = 1 AND LEFT(l.ITEMNMBR, 1) = 'F' AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND rm.VOIDDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, g.ACTDESCR, rm.VOIDDATE, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID UNION ALL SELECT MAX(SUBSTRING(w.GROUPID, 1, 4)), w.GROUPID, MAX(rm.VOIDDATE), -SUM(l.XTNDPRCE), MAX(g.ACTDESCR), MAX(g.ACTNUMBR_1), MAX(g.ACTNUMBR_2), MAX(g.ACTNUMBR_3), MAX(g.ACTNUMBR_4), MAX(w.CODEID) 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 WHERE rm.VOIDSTTS = 1 AND LEFT(l.ITEMNMBR, 1) = 'F' AND LEFT(g.ACTNUMBR_1, 1) = '7' AND LEFT(g.ACTNUMBR_1, 4) <> '7040' AND l.XTNDPRCE <> 0 AND rm.VOIDDATE BETWEEN @StartDate AND @EndDate GROUP BY w.GROUPID, g.ACTDESCR, rm.VOIDDATE, g.ACTNUMBR_1, g.ACTNUMBR_2, g.ACTNUMBR_3, g.ACTNUMBR_4, w.CODEID; /* ---------- Final result = PBI RevenueData grain ---------- */ SELECT RTRIM(r.ACTNUMBR_1) AS ACTNUMBR_1, RTRIM(r.ACTNUMBR_2) AS ACTNUMBR_2, RTRIM(r.ACTNUMBR_3) AS ACTNUMBR_3, RTRIM(r.ACTNUMBR_4) AS ACTNUMBR_4, RTRIM(r.GROUPID) AS GROUPID, m.TELastName, r.DOCDATE, RTRIM(r.CODEID) AS CODEID, r.XTNDPRCE, CASE WHEN RTRIM(r.ACTNUMBR_3) IN ('MVP', 'HBO', 'MAP') THEN m.DPTWorkCity ELSE m.DSTWorkCity END AS City, CASE WHEN RTRIM(r.ACTNUMBR_3) IN ('MVP', 'HBO', 'MAP') THEN m.DPTWorkState ELSE m.DSTWorkState END AS State, RTRIM(r.CUSTNMBR) AS CorpCode, RTRIM(r.ACTDESCR) AS ACTDESCR FROM @TMP_Rev r INNER JOIN dbo.tblTEMain1 m ON r.GROUPID = m.TEFileNo WHERE @State IS NULL OR @State = '' OR ( CASE WHEN RTRIM(r.ACTNUMBR_3) IN ('MVP', 'HBO', 'MAP') THEN m.DPTWorkState ELSE m.DSTWorkState END ) = @State ORDER BY r.DOCDATE DESC, r.ACTNUMBR_1, r.ACTNUMBR_2, r.ACTNUMBR_3, r.ACTNUMBR_4, r.GROUPID, m.TELastName, r.CODEID, City, State; -- Totals (matches PBI Total / SumXTNDPRCE) SELECT SUM(r.XTNDPRCE) AS SumXTNDPRCE FROM @TMP_Rev r INNER JOIN dbo.tblTEMain1 m ON r.GROUPID = m.TEFileNo WHERE @State IS NULL OR @State = '' OR ( CASE WHEN RTRIM(r.ACTNUMBR_3) IN ('MVP', 'HBO', 'MAP') THEN m.DPTWorkState ELSE m.DSTWorkState END ) = @State;