/* Mortgage Referrals Sent — fact sample (DatawarehouseNCM) */ SELECT 'FactMortgageReferralSent' AS FactName, COUNT(*) AS RowCnt, COUNT(DISTINCT TEFileNo) AS TECnt FROM dbo.FactMortgageReferralSent; SELECT TOP 20 dm.CorpCode, COUNT(*) AS ReferralCount, SUM(CASE WHEN f.RAMortgNotify = 1 THEN 1 ELSE 0 END) AS NotifySentCount FROM dbo.FactMortgageReferralSent f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID GROUP BY dm.CorpCode ORDER BY ReferralCount DESC; SELECT TOP 20 f.ReferID, f.TEFileNo, f.ReferralDate, f.MortgageCompanyName, f.Selected, f.RAMortgNotify, f.LoanAmount, c.ConsultantName FROM dbo.FactMortgageReferralSent f LEFT JOIN dbo.DimConsultant c ON c.DimConsultantID = f.DimConsultantID ORDER BY f.ReferralDate DESC, f.ReferID DESC;