/* Estimated Referral — Actuals only (FactEstReferralDOTA) Crystal Shared NumberVar DOTAReferral = SUM of DOTA FEE-310 / FEE-290 / FEE-291 amounts per TE. Use to verify DW fact vs Crystal paid/received amounts. Run on DatawarehouseNCM after populate. */ -- Transaction grain (as loaded) SELECT f.TEFileNo, dm.TEName, dm.CorpCode, f.CodeId, f.Amount, f.TrxDate, f.ServiceCode, f.ServiceType, f.InOut, f.Source, f.DocumentNo, f.CustVendNo, f.GPDatabase FROM dbo.FactEstReferralDOTA f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID ORDER BY f.TEFileNo, f.TrxDate, f.CodeId; -- TE rollup (matches report ActualReferralAmount / DateReceived) SELECT f.TEFileNo, dm.TEName, dm.CorpCode, SUM(f.Amount) AS ActualReferralAmount, MAX(f.TrxDate) AS DateReceived, COUNT(*) AS DotaRowCount FROM dbo.FactEstReferralDOTA f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID GROUP BY f.TEFileNo, dm.TEName, dm.CorpCode ORDER BY f.TEFileNo; -- By fee code SELECT f.CodeId, COUNT(*) AS RowCount, COUNT(DISTINCT f.TEFileNo) AS FileCount, SUM(f.Amount) AS SumAmount FROM dbo.FactEstReferralDOTA f GROUP BY f.CodeId ORDER BY f.CodeId;