/* Estimated Referral Revenue — Alternate format totals (DatawarehouseNCM) Same logic as 04 Detail; aggregates by Move Type / Service. Run on DatawarehouseNCM after populate. */ DECLARE @StartDate datetime = '2026-05-01'; DECLARE @EndDate datetime = '2026-05-31'; SELECT e.Type AS MoveType, e.ServiceCode AS Service, COUNT(*) AS [RowCount], COUNT(DISTINCT e.TEFileNo) AS FileCount, SUM(e.ResaleAmount) AS SumSalePurchase, SUM(e.CalculatedReferral) AS SumEstReferralFee, SUM(ISNULL(d.ActualReferralFee, 0)) AS SumActualReferralFee FROM dbo.FactEstReferralEstimate e 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 GROUP BY e.Type, e.ServiceCode ORDER BY e.Type, e.ServiceCode; SELECT COUNT(*) AS TotalRows, SUM(e.CalculatedReferral) AS SumEstReferralFee, SUM(ISNULL(d.ActualReferralFee, 0)) AS SumActualReferralFee FROM dbo.FactEstReferralEstimate e 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;