title: Grouping MySQL view in a sequence of rolling dates
body: <p>I've got two tables:</p>
  
  <pre><code>Table Transmission
  ------------------------------
  Id  GroupID Amount Timestamp
  1   1       5      2015-05-20 00:00:00
  2   1       4      2015-05-19 00:00:00
  3   1       10      2015-04-20 00:00:00
  4   1       7      2015-04-19 00:00:00
  5   1       9      2015-03-20 00:00:00
  6   1       2      2015-03-18 00:00:00
  
  
  Table Group
  ---------
  Id DateCreated BillStart  BillStop
  1  2015-03-15  2015-05-15 2015-06-14
  </code></pre>
  
  <p>BillStart and BillStop have a trigger on them that runs daily, if the current Date is greater than BillStop, both BillStart/BillStop increase by a month (so these are effectively the current range we are looking at).</p>
  
  <p>I already have a SQL view that can sum up a range of the transmission entries where the timestamp is between BillStart and BillStop, what I am looking to do is have another view that would effectively hold the sums of transmissions of past months. i.e. if a BillStart/BillStop goes from 2015-05-15 to 2015-06-14, then the view would know to group past transmissions from the ranges of 2015-4-15 to 20-15-14, 2015-3-15 to 2015-4-14, and so on, up until the last range containing the groups creation date.</p>
  
  <p>Ideally the view would look like</p>
  
  <pre><code>-------------------------------------
  GroupId   Sum   BillStart   BillStop
  1         9     2015-05-15  2015-06-14
  1         17     2015-04-15  2015-05-14
  1         11     2015-03-15  2015-04-14
  </code></pre>
  
  <p>Is there a better way to do this?</p>
  
  <p>Another option I was considering was a table for GroupBillRanges that would entail:</p>
  
  <pre><code>GroupBillRange
  --------------
  Id GroupId BillStart   BillStop
  1  1       2015-03-15  2015-04-14
  2  1       2015-04-15  2015-05-14
  3  1       2015-05-15  2015-06-14
  </code></pre>
  
  <p>And this would be added when each bill range gets updated by the month.  With this I would just be able to match all <code>Transmission.GroupId</code> to <code>GroupDateRange.GroupId</code></p>