title: TSQL - Get value between dates in data warehouse dimension
body: <p>I have the beginnngs of a data warehouse, that contains (among other tables)</p>
  <ul>
  <li>date table with 200 years worth of dates to join with, so no need to
  build a &quot;dynamic&quot; date table</li>
  <li>dbo.Dim_Items (see below)</li>
  </ul>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ItemNumber</th>
  <th>QOH</th>
  <th>EffectiveDate</th>
  <th>ExpirationDate</th>
  <th>IsCurrentRecord</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>372620</td>
  <td>24</td>
  <td>2021-12-11 05:34:09.000</td>
  <td>9999-12-31 00:00:00.000</td>
  <td>1</td>
  </tr>
  <tr>
  <td>372620</td>
  <td>11</td>
  <td>2021-12-09 05:34:11.000</td>
  <td>2021-12-11 05:34:09.000</td>
  <td>0</td>
  </tr>
  <tr>
  <td>372620</td>
  <td>9</td>
  <td>2021-12-07 05:34:20.000</td>
  <td>2021-12-09 05:34:11.000</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like to find the QOH for each day between 2021-12-07 and today (assume 2021-12-13, for brevity), so that my result looks like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Date</th>
  <th>ItemNumber</th>
  <th>QOH</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-12-07</td>
  <td>372620</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-12-08</td>
  <td>372620</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-12-09</td>
  <td>372620</td>
  <td>11</td>
  </tr>
  <tr>
  <td>2021-12-10</td>
  <td>372620</td>
  <td>11</td>
  </tr>
  <tr>
  <td>2021-12-11</td>
  <td>372620</td>
  <td>24</td>
  </tr>
  <tr>
  <td>2021-12-12</td>
  <td>372620</td>
  <td>24</td>
  </tr>
  <tr>
  <td>2021-12-13</td>
  <td>372620</td>
  <td>24</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The closest I have come is the query below, but it is not working the way I want it to (note the zeros on dates that don't line up with the Items dimension)</p>
  <pre><code>DECLARE @START_DATE date,
          @END_DATE date
  
  SET @START_DATE = '20211207'
  SET @END_DATE = GETDATE()   -- '20211213'
  
  SELECT CAL.TheDate,
          I.ItemNumber,
          I.QOH
  FROM dbo.Dim_Calendar CAL
      LEFT OUTER JOIN dbo.Dim_Items I ON CAL.TheDate &gt;= CAST(I.EffectiveDate as date)
          AND CAL.TheDate &lt;= CAST(I.EffectiveDate as date)
          AND I.ItemNumber = 372620
  WHERE CAL.TheDate &gt;= @START_DATE
      AND CAL.TheDate &lt;= @END_DATE
  ORDER BY CAL.TheDate,
              I.ItemNumber
          
          
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Date</th>
  <th>ItemNumber</th>
  <th>QOH</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-12-07</td>
  <td>372620</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-12-08</td>
  <td>372620</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2021-12-09</td>
  <td>372620</td>
  <td>11</td>
  </tr>
  <tr>
  <td>2021-12-10</td>
  <td>372620</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2021-12-11</td>
  <td>372620</td>
  <td>24</td>
  </tr>
  <tr>
  <td>2021-12-12</td>
  <td>372620</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2021-12-13</td>
  <td>372620</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am new to data warehousing as a whole, so I am not sure how to build this query correctly.</p>