/* Mortgage Referrals Sent — summary (DatawarehouseNCM) Counts / loan totals by client and mortgage company. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-12-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @DimConsultantID int = NULL; SELECT dm.CorpCode, dm.FormalName, f.MortgageCompanyName, COUNT(*) AS ReferralCount, SUM(CASE WHEN f.Selected = 1 THEN 1 ELSE 0 END) AS SelectedCount, SUM(CASE WHEN f.RAMortgNotify = 1 THEN 1 ELSE 0 END) AS NotifySentCount, SUM(CAST(ISNULL(f.LoanAmount, 0) AS bigint)) AS LoanAmountSum FROM dbo.FactMortgageReferralSent f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID WHERE (@StartDate IS NULL OR f.ReferralDate >= @StartDate) AND (@EndDate IS NULL OR f.ReferralDate <= @EndDate) AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 0 AND dm.CorpCode = @CorpCode) OR (@ParentRollup = 1 AND (dm.ParentCompany = @CorpCode OR dm.CorpCode = @CorpCode)) ) AND (@DimConsultantID IS NULL OR f.DimConsultantID = @DimConsultantID) GROUP BY dm.CorpCode, dm.FormalName, f.MortgageCompanyName ORDER BY dm.CorpCode, ReferralCount DESC, f.MortgageCompanyName;