/* Estimated Referral Revenue — Crystal layout totals (DatawarehouseNCM) Same rows as 07 Crystal Detail. Matches the footers in EstReferralRevenue.xls: 1. Total for each client, inside Departure (DEPT) and Destination (DEST) Estimated Referral, Calc'd Referral, Actual Referral Amount 2. Totals for Departure Referrals / Totals for Destination Referrals 3. Total Referrals 4. Average Months to COE across the detail rows Run on DatawarehouseNCM after Ops load. */ DECLARE @StartDate datetime = '2026-10-01'; DECLARE @EndDate datetime = '2026-11-02'; ;WITH Detail AS ( SELECT e.Type, e.CorpCode, dm.FormalName AS Client, e.TEFileNo, e.MonthToCOE, e.EstimatedReferral, e.CalculatedReferral, ISNULL(d.ActualReferralAmount, 0) AS ActualReferralAmount FROM dbo.FactEstReferralEstimate e LEFT JOIN dbo.DimMain dm ON dm.DimMainID = e.DimMainID 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 AND NOT (e.CorpCode = 'PNCX' AND e.FileAddDate >= '2011-07-01') ) SELECT Type, Client, CorpCode, COUNT(*) AS [RowCount], COUNT(DISTINCT TEFileNo) AS FileCount, SUM(EstimatedReferral) AS SumEstimatedReferral, SUM(CalculatedReferral) AS SumCalculatedReferral, SUM(ActualReferralAmount) AS SumActualReferralAmount FROM Detail GROUP BY Type, Client, CorpCode ORDER BY Type, Client, CorpCode; ;WITH Detail AS ( SELECT e.Type, e.TEFileNo, e.MonthToCOE, e.EstimatedReferral, e.CalculatedReferral, ISNULL(d.ActualReferralAmount, 0) AS ActualReferralAmount 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 AND NOT (e.CorpCode = 'PNCX' AND e.FileAddDate >= '2011-07-01') ) SELECT Type, CASE Type WHEN 'DEPT' THEN 'Totals for Departure Referrals' WHEN 'DEST' THEN 'Totals for Destination Referrals' END AS SectionTotal, COUNT(*) AS [RowCount], COUNT(DISTINCT TEFileNo) AS FileCount, SUM(EstimatedReferral) AS SumEstimatedReferral, SUM(CalculatedReferral) AS SumCalculatedReferral, SUM(ActualReferralAmount) AS SumActualReferralAmount FROM Detail GROUP BY Type ORDER BY Type; ;WITH Detail AS ( SELECT e.TEFileNo, e.MonthToCOE, e.EstimatedReferral, e.CalculatedReferral, ISNULL(d.ActualReferralAmount, 0) AS ActualReferralAmount 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 AND NOT (e.CorpCode = 'PNCX' AND e.FileAddDate >= '2011-07-01') ) SELECT COUNT(*) AS [RowCount], COUNT(DISTINCT TEFileNo) AS FileCount, SUM(EstimatedReferral) AS TotalReferrals_Estimated, SUM(CalculatedReferral) AS TotalReferrals_Calculated, SUM(ActualReferralAmount) AS TotalReferrals_Actual, AVG(CAST(MonthToCOE AS decimal(18, 6))) AS AverageMonthsToCOE FROM Detail;