/* Estimated Referral Revenue — totals (DatawarehouseNCM) Same source as 01 Detail; aggregates by Type (DEPT / DEST). Run on DatawarehouseNCM after populate. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-12-31'; SELECT e.Type, COUNT(*) AS [RowCount], COUNT(DISTINCT e.TEFileNo) AS FileCount, SUM(e.ResaleAmount) AS SumResaleAmount, SUM(e.CalculatedReferral) AS SumCalculatedReferral, SUM(e.EstimatedReferral) AS SumEstimatedReferral, SUM(ISNULL(d.ActualReferralAmount, 0)) AS SumActualReferralAmount, COUNT(d.ActualReferralAmount) AS FilesWithActualReferral FROM dbo.FactEstReferralEstimate e LEFT JOIN ( SELECT TEFileNo, SUM(Amount) AS ActualReferralAmount FROM dbo.FactEstReferralDOTA GROUP BY TEFileNo ) d ON d.TEFileNo = e.TEFileNo WHERE e.EstCOE >= @StartDate AND e.EstCOE <= @EndDate GROUP BY e.Type ORDER BY e.Type; SELECT COUNT(*) AS TotalRows, COUNT(DISTINCT e.TEFileNo) AS TotalFiles, SUM(e.EstimatedReferral) AS SumEstimatedReferral, SUM(ISNULL(d.ActualReferralAmount, 0)) AS SumActualReferralAmount FROM dbo.FactEstReferralEstimate e LEFT JOIN ( SELECT TEFileNo, SUM(Amount) AS ActualReferralAmount FROM dbo.FactEstReferralDOTA GROUP BY TEFileNo ) d ON d.TEFileNo = e.TEFileNo WHERE e.EstCOE >= @StartDate AND e.EstCOE <= @EndDate;