title: SQL - Creating stored procedure to loop through dates to calculate a measure
body: <p>Problem - I'm trying to create a stored procedure that will run quarterly to calculate a metric (in this case, a sum) over a moving year-long window of monthly historical data, where the calculation starts from three months prior to the first day of the quarter and looks back a year prior to that date. So, when the procedure runs on 4/1/2015, I want to calculate the metric from 1/1/2014 through 12/31/2014. On 7/1/2015, I want to calculate the metric from 4/1/2014 through 3/31/2015. And on and on and on. (It will also have to include historical quarter-based calculations of the metric as well.)</p>
  
  <p>Because this is an analysis that will be run regularly, I'm needing to automate the process, and my thought is that some kind of loop is appropriate. I'm new to programming in SQL - my experience is almost exclusively querying - and any help would be appreciated. </p>
  
  <p>As it stands, my data look like this (apologies for the length):</p>
  
  <pre><code>ID              Month            Metric
  123456          1/1/2011         7           
  123456          2/1/2011         4          
  123456          3/1/2011         8          
  123456          4/1/2011         2         
  123456          5/1/2011         0          
  123456          6/1/2011         7  
  123456          7/1/2011         4
  123456          8/1/2011         0         
  123456          9/1/2011         7
  123456          10/1/2011        4
  123456          11/1/2011        6           
  123456          12/1/2011        0 
  123456          1/1/2012         0           
  123456          2/1/2012         2          
  123456          3/1/2012         7          
  123456          4/1/2012         3         
  123456          5/1/2012         5          
  123456          6/1/2012         6  
  123456          7/1/2012         5
  123456          8/1/2012         5         
  123456          9/1/2012         1
  123456          10/1/2012        5
  123456          11/1/2012        2           
  123456          12/1/2012        7 
  123456          1/1/2013         5           
  123456          2/1/2013         7          
  123456          3/1/2013         5   
  987654          1/1/2011         2           
  987654          2/1/2011         0          
  987654          3/1/2011         7          
  987654          4/1/2011         5         
  987654          5/1/2011         6          
  987654          6/1/2011         8  
  987654          7/1/2011         4
  987654          8/1/2011         4         
  987654          9/1/2011         3
  987654          10/1/2011        3
  987654          11/1/2011        3           
  987654          12/1/2011        2 
  987654          1/1/2012         5           
  987654          2/1/2012         2          
  987654          3/1/2012         3          
  987654          4/1/2012         8         
  987654          5/1/2012         5          
  987654          6/1/2012         7  
  987654          7/1/2012         6
  987654          8/1/2012         0         
  987654          9/1/2012         3
  987654          10/1/2012        6
  987654          11/1/2012        6           
  987654          12/1/2012        6 
  987654          1/1/2013         0           
  987654          2/1/2013         4          
  987654          3/1/2013         4 
  </code></pre>
  
  <p>It's easy enough to write a query to get the results for any one quarter (Q1 2012):</p>
  
  <pre><code>SELECT DISTINCT ID, '01' AS Quarter, '2012' AS Year, SUM(Metric) AS Metric
  FROM TABLE_1
  WHERE (Month &gt;= '1/1/2011' AND Month &lt; '1/1/2012')
  GROUP BY ID
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>ID        Quarter    Year     Metric
  123456    01         2012     49
  </code></pre>
  
  <p>Writing a variant of this for every quarter of analysis is obviously inefficient (and wouldn't work anyway for what I'm trying to create), so the goal is to write some kind of loop to automate the process. </p>
  
  <p>The desired output would look like this:</p>
  
  <pre><code>ID        Quarter    Year     Metric
  123456    01         2012     49
  123456    02         2012     39
  123456    03         2012     44
  123456    04         2012     44
  123456    01         2013     51
  987654    01         2012     47
  987654    02         2012     48
  987654    03         2012     49
  987654    04         2012     47
  987654    01         2013     57
  </code></pre>
  
  <p>I'm stuck, and my lack of experience programming in SQL is proving to be a challenge. Any suggestions on how to proceed? Thanks in advance!</p>