title: Is this is the best way to write SQL for this report?
body: <p>We have <code>orders</code> data and we need to generate <code>Weekly - Custom date Range of 7 Days</code>report using <code>SQL</code>. The report will display weekly <code>count</code> of Orders. The user will select an <code>End Date</code> and we need to create 12 DateRange units based on that date. For example the user selects <code>1/24/2013</code> we need 12 Points/Units as:</p>
  
  <pre><code>Point12 = End Date - 7 Days
  Point11 = End Date - 14 Days
  Point10 = End Date - 21 Days
  Point9  = End Date - 28 Days
  .
  .
  Point1  = End Date - X Days
  </code></pre>
  
  <p><strong>Our solution</strong>:</p>
  
  <p>We are planning to create a temporary table that table will have 12 Rows. Each row will have data like (we will calculate the startdate and endDate for each Point):</p>
  
  <pre><code>Point   StartDate           EndDate      TotalOrders
  Point12  2013-01-24          2013-01-30   
  Point11  2013-01-17          2013-01-23  
  </code></pre>
  
  <p>After this we will get the <code>count</code> of Orders for each row. </p>
  
  <p><strong>Is this is a good solution to this problem or it can be optimized?</strong></p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The weekly DateRange will be a <code>Custom date Range of 7 Days</code>.</p>
  
  <p><img src="https://i.stack.imgur.com/O0y84.png" alt="enter image description here"></p>