title: SQL Query to Count Items Within Date Range?
body: <p>To start, I know just enough at this point to get myself into trouble.  I am working on writing a query for an AS/400 system.  I have a file that contains a start date and end date.  What I want to do is create a query that can count how many line items were open for a range of dates. (for my application open is defined as: <code>DATE &gt; START_DATE</code> AND (<code>DATE &lt;= END_DATE OR END_DATE = '0'</code>)).  I can get this working for manually setting "DATE", but I'm trying to go back and pull this information in bulk historically.  What I'm having trouble with is having it run this query against a list of dates.  What I want is to input the historical interval (say <code>CURRENT_DATE - 5</code>) and output:</p>
  
  <pre><code>Date             Open
  20200206         30,000
  20200207         31,000
  20200210         29,675
  20200211         31,375
  20200212         32,000
  </code></pre>
  
  <p>What I use for 'one-day-at-a-time' query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
          COUNT(*) 
      FROM
          MYFILE
      WHERE 
          START_DATE &lt; '20200210' 
      AND (
              END_DATE &gt;= '20200210' 
          OR  END_DATE = '0'
          )
  </code></pre>
  
  <p>This works great, but is time consuming.  From here I have no idea how to transition to the desired output...or even if it's possible...but it would speed things up for me.  Thanks in advance.</p>
  
  <p>P.S. any help is welcomed, if you know how to accomplish this in mySQL etc. please share.  I can take that and run with it in terms of getting it to work for AS/400 specifically.  That is how I've gotten to where I'm at now.</p>
  
  <p>**************** Edit for clarifications
  1. Current Code
      The current-day code provided is all that I have.  I don't know where to start
      on what I am asking for.
  2. Example Data:</p>
  
  <pre><code>Line Item       START_DATE        END_DATE
  1               20200206          20200210
  2               20200207          20200210
  3               20200207          20200207
  </code></pre>
  
  <ol start="3">
  <li>Expected Output:</li>
  </ol>
  
  <pre><code>DATE           COUNT
  20200205       0
  20200206       0
  20200207       1
  20200210       2
  20200211       0
  </code></pre>
  
  <ol start="4">
  <li>DBMS I've updated the tag to include DB2.  5. I don't believe GROUP BY will work.<br>
  Basically I am looking for this to be calculated in a iterative-esque fashion.  Coming from my python/java background this makes sense to me.  I'm not looking for everything that was open 5 days ago, I want to start 5 days ago and calculate how many line items were open each of those 5 days.  If this is in fact possible with GROUP, please elaborate and show me the error of my ways.  Meanwhile, I will begin messing with grouping to this end.</li>
  </ol>