title: Left Outer Join With Date Range CTE Not Working As I Expect
body: <p>I'm using the following LOJ query which uses a CTE to generate a range of dates:</p>
  
  <pre><code>Declare @inquiryStartDate DateTime;
  Declare @inquiryEndDate DateTime;
  Declare @inquiryMortgageNumber nvarchar(50);
  
  SET @inquiryStartDate = '2013-07-01';
  SET @inquiryEndDate = '2013-07-31';
  SET @inquiryMortgageNumber = '12345678';
  
  With DateRange As (
      SELECT ID, Date
      FROM     d_Dates
      WHERE  (Date BETWEEN @inquiryStartDate AND @inquiryEndDate)
  )
  Select DateRange.ID, DateRange.Date,f_MortgageSnapshots.MortgageNumber, f_MortgageSnapshots.Investor_ID
  From DateRange
  LEFT OUTER JOIN f_MortgageSnapshots On DateRange.ID = f_MortgageSnapshots.SnapshotDate_ID
  WHERE f_MortgageSnapshots.MortgageNumber = @inquiryMortgageNumber;
  </code></pre>
  
  <p>And I'm getting this:</p>
  
  <p><img src="https://i.stack.imgur.com/h2JTg.png" alt="enter image description here"></p>
  
  <p>But I want this:</p>
  
  <p><img src="https://i.stack.imgur.com/WpSKt.png" alt="enter image description here"></p>
  
  <p>What am I doing wrong?  </p>
  
  <p>Quick note, There are just 2 rows in the f_MortgageSnapshots table for mortgage 12345678.</p>
  
  <p><img src="https://i.stack.imgur.com/ebZcy.png" alt="enter image description here"></p>
  
  <hr>
  
  <h2>Solution Here!</h2>
  
  <p>Declare @inquiryStartDate DateTime;
  Declare @inquiryEndDate DateTime;
  Declare @inquiryMortgageNumber nvarchar(50);</p>
  
  <p>SET @inquiryStartDate = '2013-07-01';
  SET @inquiryEndDate = '2013-07-31';
  SET @inquiryMortgageNumber = '7078575';</p>
  
  <pre><code>With DateRange As (
  SELECT ID, d_Dates.Date
  FROM     d_Dates
  WHERE  (d_Dates.Date BETWEEN @inquiryStartDate AND @inquiryEndDate)
   )
  Select DateRange.ID, DateRange.Date,f_MortgageSnapshots.MortgageNumber, f_MortgageSnapshots.Investor_ID
  From DateRange Left Join f_MortgageSnapshots 
       On DateRange.ID = f_MortgageSnapshots.SnapshotDate_ID
          And  MortgageNumber = @inquiryMortgageNumber;
  </code></pre>