title: TSQL - Iterate through list of dates, group data, and insert into table
body: <p>So I have data being imported into a table in the following format. For any months missing, assume the MM, App_Amt, ClaimCnt, and UnqPtCnt columns are 0:</p>
  
  <pre><code>location|health_plan| plan_type | pcp |  MM  |App_Amt|ClaimCnt|UnqPtCnt|Date
  SoJ     |BLUE SHIELD|    COM    | 2384|  18  | 0.00  |   0    |   0    |2014-01-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  20  |1430.08|   1    |   1    |2014-02-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 468.69|   4    |   3    |2014-03-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 184.12|   2    |   2    |2014-04-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  19  | 184.12|   2    |   2    |2014-05-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  15  |2321.79|   3    |   3    |2014-06-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  18  | 258.18|   2    |   2    |2014-07-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  22  | 258.18|   2    |   2    |2014-08-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  83  | 395.29|   3    |   2    |2014-09-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  28  | 258.18|   2    |   2    |2014-10-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  32  | 571.02|   4    |   4    |2014-11-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  14  | 258.18|   2    |   2    |2014-12-01    
  </code></pre>
  
  <p>I need to calculate a rolling/trailing 12 months based on this dataset. I will take the average of MM, sum of App_Amt, sum of ClaimCnt, and sum of UnqPtCt and group by location, health_plan, plan_type, and PCP and I have a list of date ranges I want to iterate through:  </p>
  
  <p>I am getting this list of dates from the source table itself. It currently has the 1st of the month from the last 3 years. Next month, the data will shift over one month. For example, right now it has a minimum date of 01/01/2014 and maximum date of 11/01/2016 but next month in April the minimum date will be 02/01/2014 and maximum date will be 12/01/2016.</p>
  
  <pre><code>declare @end_dt date
  set @end_dt = dateadd(month,-4,getdate())
  
  select distinct [date] as start_dt,DATEADD(month,11,[date]) end_dt from [dbo].[OON_Summary]
  
  select * from @Rolling where end_dt &lt;= @end_dt
  
  
   start_dt | end_dt
  2014-01-01|2014-12-01
  2014-02-01|2015-01-01
  2014-03-01|2015-02-01
  2014-04-01|2015-03-01
  2014-05-01|2015-04-01
  2014-06-01|2015-05-01
  2014-07-01|2015-06-01
  2014-08-01|2015-07-01
  2014-09-01|2015-08-01
  2014-10-01|2015-09-01
  2014-11-01|2015-10-01
  2014-12-01|2015-11-01
  2015-01-01|2015-12-01
  2015-02-01|2016-01-01
  2015-03-01|2016-02-01
  2015-04-01|2016-03-01
  2015-05-01|2016-04-01
  2015-06-01|2016-05-01
  2015-07-01|2016-06-01
  2015-08-01|2016-07-01
  2015-09-01|2016-08-01
  2015-10-01|2016-09-01
  2015-11-01|2016-10-01
  2015-12-01|2016-11-01
  </code></pre>
  
  <p>So far I have this simple query but it's far too manual and I know there must be a better way to automate this.</p>
  
  <pre><code>declare @start_dt date 
  declare @end_dt date 
  set @start_dt = '2014-01-01'
  set @end_dt = '2014-12-01'
  
  insert into [dbo].[OON_Rolling12]
    SELECT 
    ,[location]
    ,[health plan]
    ,[plan_type]
    ,[pcp]
    ,AVG([MM]) [MM]
    ,SUM([App_Amt]) [App_Amt]
    ,SUM([ClaimCnt]) [ClaimCnt]
    ,SUM([UnqPtCnt]) UnqPtCnt
    ,@end_dt as AsOfDate
  FROM [dbo].[OON_Summary]
  WHERE [Date] &gt;= @start_dt AND [Date] &lt;= @end_dt
  GROUP BY 
     [location]
    ,[health plan]
    ,[plan_type]
    ,[pcp]
  </code></pre>
  
  <p>Running the query once will produce the first line in the output below. Essentially I am grouping all of the data from Jan 1 2014 through Dec 1 2014 and calling it Dec 2014 data. How can I iterate through that list of dates and generate the rest of the rows automatically?</p>
  
  <pre><code>location|health_plan| plan_type | pcp |  MM  |App_Amt|ClaimCnt|UnqPtCnt |AsOfDate
  SoJ     |BLUE SHIELD|    COM    | 2384|  25  |6565.83|   27   |   25    |2014-12-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  24  |6565.83|   27   |   25    |2015-01-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  22  |5135.75|   26   |   24    |2015-02-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  20  |4689.06|   22   |   21    |2015-03-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  19  |4504.94|   20   |   19    |2015-04-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  17  |4320.82|   18   |   17    |2015-05-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  16  |1999.03|   15   |   14    |2015-06-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  14  |1740.85|   13   |   12    |2015-07-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  13  |1482.67|   11   |   10    |2015-08-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  6   |1087.38|    8   |   8     |2015-09-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  3   |829.20 |    6   |   6     |2015-10-01
  SoJ     |BLUE SHIELD|    COM    | 2384|  0   |258.18 |    2   |   2     |2015-11-01    
  SoJ     |BLUE SHIELD|    COM    | 2384|  0   | 0.00  |    0   |   0     |2015-12-01    
  </code></pre>