title: SQL LAG function with JOIN - must pull data from previous row even if data is null
body: <p>I have three tables:</p>
  
  <p>1) <code>Calendar</code></p>
  
  <ul>
  <li>This table has a single entry for each date (this is actually part of a larger table with daily records - I'm simplifying for this context)</li>
  </ul>
  
  <p>2) <code>Reading_Type</code></p>
  
  <ul>
  <li><code>Reading_Type_ID</code></li>
  <li><code>Type_Name</code></li>
  </ul>
  
  <p>3) <code>Readings</code></p>
  
  <ul>
  <li><code>Reading_ID</code></li>
  <li><code>Reading_Type_ID</code></li>
  <li><code>Reading_Date</code></li>
  <li><code>Reading</code></li>
  </ul>
  
  <p>Only one entry is allowed for a given <code>Reading_Type_ID</code> and <code>Reading_Date</code>, but not all dates have a reading.</p>
  
  <p>I need a query that returns all data from <code>Readings</code>, plus yesterday's reading for that <code>Reading_Type_ID</code>.</p>
  
  <p>If the previous day (yesterday) has no reading, <code>Yesterday_Reading</code> should be 0 (it should <em>not</em> just pull the reading from the previous row in the table if that row is > 1 day prior).</p>
  
  <p>What I have so far:</p>
  
  <pre><code>SELECT
        r.Reading_ID
      , r.Reading_Type_ID
      , r.Reading
      , r.Reading_Date
      , LAG(r.Reading, 1, 0)
          OVER (PARTITION BY r.Reading_Type_ID ORDER BY r.Reading_Date)
          AS Yesterday_Reading
  FROM
      Calendar c
  LEFT JOIN
      Readings r ON c.Date = r.Reading_Date
  </code></pre>
  
  <p>This LAG function pulls from the last row (by date) in the <code>Readings</code> table, not strictly from "yesterday".  I've tried some permutations (<code>ORDER BY c.Date</code>, adding rows from <code>c</code> to the <code>SELECT</code> clause, etc), but I don't know how to fix this.</p>