Databases
StackOverflow2013
Postsdbo
POLeft Outer Join With Date Range CTE Not Working As I Expect
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLeft Outer Join With Date Range CTE Not Working As I Expect
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload