/* Estimated Referral Revenue — Crystal layout detail (DatawarehouseNCM) Sample: EstReferralRevenue.xls (Crystal EstReferralRevenue.rpt) BackOffice: frmRptEstReferralRevenue Same facts as 01 Detail. This layout matches the workbook: client group (DimMain.FormalName) Departure Referrals (DEPT), then Destination Referrals (DEST) Service — DOTA service on the received referral; blank when the actual amount is 0 Est COE window (header: Est. COE Dates From ... to ...) Service: when a file has several DOTA lines, prefer the service whose DimTEFullServiceList.ServiceGroupCode matches the row type (DEPT / DEST), then the latest transaction, then the largest amount. Actuals stay the Crystal DOTAReferral total: SUM of FEE-310 / FEE-290 / FEE-291 by TEFileNo. Date Received is the latest of those transactions, and is blank when the actual amount is 0. Crystal also drops PNCX files added on or after 2011-07-01 (pr_EstReferralRevenue). Run on DatawarehouseNCM after Ops load. */ DECLARE @StartDate datetime = '2026-10-01'; DECLARE @EndDate datetime = '2026-11-02'; SELECT dm.FormalName AS Client, e.CorpCode, e.TEFileNo AS [TE File No], dm.TEName AS [TE Name], svc.ServiceCode AS Service, e.EstCOE AS [Est COE], e.MonthToCOE AS [Months to COE], e.ResaleAmount AS [Resale Amount], e.Type, e.EstimatedReferral AS [Estimated Referral], e.ReferralPercentage AS [Referral %], e.AgentCommission AS [Agent Comm %], e.CalculatedReferral AS [Calc'd Referral], e.ActualCOE AS [Actual COE], ISNULL(d.ActualReferralAmount, 0) AS [Actual Referral Amount], CASE WHEN ISNULL(d.ActualReferralAmount, 0) = 0 THEN NULL ELSE d.DateReceived END AS [Date Received], 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 OUTER APPLY ( SELECT TOP (1) f.ServiceCode FROM dbo.FactEstReferralDOTA f LEFT JOIN dbo.DimTEFullServiceList s ON s.TEFileNo = f.TEFileNo AND s.ServiceCode = f.ServiceCode WHERE f.TEFileNo = e.TEFileNo AND NULLIF(RTRIM(f.ServiceCode), '') IS NOT NULL AND ISNULL(d.ActualReferralAmount, 0) <> 0 ORDER BY CASE WHEN s.ServiceGroupCode = e.Type THEN 0 ELSE 1 END, f.TrxDate DESC, ABS(f.Amount) DESC, f.ServiceCode ) svc WHERE e.EstCOE >= @StartDate AND e.EstCOE <= @EndDate AND NOT (e.CorpCode = 'PNCX' AND e.FileAddDate >= '2011-07-01') ORDER BY e.Type, dm.FormalName, e.CorpCode, e.EstCOE, e.TEFileNo;