/* Estimated Referral Revenue — Alternate format (DatawarehouseNCM) Sample: Rules/Samples/EstimatedReferal revenue2.docx (screenshot) Columns: File # | Transferee | Service | Sale/Purchase | Move Type | Referral % | Agent % | Est Referral Fee | Actual COE | Actual Referral Fee | Consultant Est Referral Fee = CalculatedReferral (sale × agent% × referral%) Service: HBO for DEPT, DPR for DEST (stored on fact). Date filter uses ActualCOE (date shown on this format). Switch to EstCOE to match Crystal EstReferralRevenue.rpt window. */ DECLARE @StartDate datetime = '2026-05-01'; DECLARE @EndDate datetime = '2026-05-31'; SELECT e.TEFileNo AS [File #], dm.TEName AS Transferee, e.ServiceCode AS Service, e.ResaleAmount AS [Sale/Purchase], e.Type AS [Move Type], e.ReferralPercentage AS [Referral %], e.AgentCommission AS [Agent %], e.CalculatedReferral AS [Est Referral Fee], e.ActualCOE AS [Actual COE], ISNULL(d.ActualReferralFee, 0) AS [Actual Referral Fee], c.ConsultantName AS Consultant, e.CorpCode, e.DimMainID, e.DimConsultantID FROM dbo.FactEstReferralEstimate e LEFT JOIN dbo.DimMain dm ON dm.DimMainID = e.DimMainID LEFT JOIN dbo.DimConsultant c ON c.DimConsultantID = e.DimConsultantID LEFT JOIN ( SELECT TEFileNo, SUM(Amount) AS ActualReferralFee FROM dbo.FactEstReferralDOTA GROUP BY TEFileNo ) d ON d.TEFileNo = e.TEFileNo WHERE e.ActualCOE IS NOT NULL AND e.ActualCOE >= @StartDate AND e.ActualCOE <= @EndDate ORDER BY e.ActualCOE, e.TEFileNo;