title: Parameter month selection: Make a query that shows previous month, 12 months ago and last 12 months average
body: <p>I wanted to play around with my <strong>Total_Sales</strong> table.
  This is how the data looks like (using SQL Server 2008 R2)</p>
  
  <pre><code>Name    Year  Month  Sales
  ------  ----  -----  -----
  Alfred  2011  1      100
  Alfred  2011  2      200
  Alfred  2011  3      300
  Alfred  2011  4      400
  Alfred  2011  5      500
  Alfred  2011  6      600
  Alfred  2011  7      700
  Alfred  2011  8      800
  Alfred  2011  9      900
  Alfred  2011  10     500
  Alfred  2011  11     500
  Alfred  2011  12     500
  </code></pre>
  
  <p>The SQL query I want to create should display the data like this:</p>
  
  <pre><code>Name    Year  Month  Sales Prev_Month Month_Last_Year_Sales Last_12_Month_AVG
  ------  ----  -----  ----- ---------- --------------------- -----------------
  Alfred  2011  1      100   NULL       (year 2010, month 1)  (2010_01 to 2011_01)/(12)
  Alfred  2011  2      200   100        (year 2010, month 2)  (2010_02 to 2011_02)/(12)
  Alfred  2011  3      300   200        (year 2010, month 3)  (2010_03 to 2011_03)/(12)
  Alfred  2011  4      400   300        (year 2010, month 4)  (2010_04 to 2011_04)/(12)
  Alfred  2011  5      500   400        (year 2010, month 5)  (2010_05 to 2011_05)/(12)
  Alfred  2011  6      600   500        (year 2010, month 6)  (2010_06 to 2011_06)/(12)
  Alfred  2011  7      700   600        (year 2010, month 7)  (2010_07 to 2011_07)/(12)
  Alfred  2011  8      800   700        (year 2010, month 8)  (2010_08 to 2011_08)/(12)
  Alfred  2011  9      900   800        (year 2010, month 9)  (2010_09 to 2011_09)/(12)
  Alfred  2011  10     500   900        (year 2010, month 10) (2010_10 to 2011_10)/(12)
  Alfred  2011  11     500   500        (year 2010, month 11) (2010_11 to 2011_11)/(12)
  Alfred  2011  12     500   500        (year 2010, month 12) (2010_12 to 2011_12)/(12)
  </code></pre>
  
  <p>To copy the prior month I am using this: <a href="https://stackoverflow.com/questions/6873976/copy-prior-month-value-and-insert-into-new-row">Copy prior month value and insert into new row</a>  </p>
  
  <pre><code>SELECT
  TS.name,
  TS.year,
  TS.month,
  TS.sales,
  COALESCE(TS2.sales, 0) AS prior_month_sales
  FROM
  TotalSales TS
  LEFT OUTER JOIN TotalSales TS2 ON
  TS2.name = TS.name AND
  (
      (TS2.year = TS.year AND TS2.month = TS.month - 1) OR
      (TS.month = 1 AND TS2.month = 12 AND TS2.year = TS.year - 1)
  )
  </code></pre>
  
  <p>The NULL in Prev_Month is to show that the start of the Total_Sales was in year 2011 month 1, so no prior data for this example. </p>
  
  <p>I am planning to use a parameter, where you select a month.
  <br>
  Thanks for any help!</p>