/* Estimated Referral Revenue — detail (DatawarehouseNCM) BackOffice: frmRptEstReferralRevenue / Crystal EstReferralRevenue.rpt Estimates: dbo.FactEstReferralEstimate Actuals: dbo.FactEstReferralDOTA (Crystal DOTAReferral) Run on DatawarehouseNCM after Ops load: EXEC dbo.PrcStageOpsSources; EXEC dbo.PrcPopulateOpsFromStage; */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-12-31'; SELECT e.CorpCode, e.TEFileNo, dm.TEName, e.EstCOE, e.ResaleAmount, e.Type, e.ActualCOE, e.ReferralPercentage, e.AgentCommission, e.CalculatedReferral, e.MonthToCOE, e.EstimatedReferral, d.ActualReferralAmount, d.DateReceived, e.DimMainID FROM dbo.FactEstReferralEstimate e LEFT JOIN dbo.DimMain dm ON dm.DimMainID = e.DimMainID LEFT JOIN ( SELECT TEFileNo, SUM(Amount) AS ActualReferralAmount, MAX(TrxDate) AS DateReceived FROM dbo.FactEstReferralDOTA GROUP BY TEFileNo ) d ON d.TEFileNo = e.TEFileNo WHERE e.EstCOE >= @StartDate AND e.EstCOE <= @EndDate ORDER BY e.Type, e.EstCOE, e.TEFileNo;