title: Group By MONTH AND Quarter AND SUM previous 11 Months
body: <p>Hello I Have been struggling with this for about a day now :/. What I am trying to accomplish is return month values and previous 11 months int the quarter all in the same query. </p>
  
  <p>So I have a table </p>
  
  <pre><code>CREATE TABLE Test
  (
      Id INT,
      Date DATETIME2,
      Value DECIMAL(15,4)
  )
  </code></pre>
  
  <p>I am trying to get data like :</p>
  
  <pre><code>          This Month   Previous_11_Months
  January     100        1100
  February    123        1123
  March       211        2123
  </code></pre>
  
  <p>My Actual Sql :</p>
  
  <pre><code>DECLARE @endDate DATETIME2 = '6-30-2017',
      @plantId INT       = 1
  DECLARE @endDateMinusYear DATETIME2 = DATEADD(YEAR,-1,@endDate),
          @firstDayOfThisQuarter DATETIME2,
          @firstDayOfThisQuarterLastYear DATETIME2
  
  SELECT @firstDayOfThisQuarter = DATEADD(QQ, DATEDIFF(QQ , 0, @endDate), 0)
  SELECT @firstDayOfThisQuarterLastYear = DATEADD(QQ, DATEDIFF(QQ , 0, @endDateMinusYear), 0)
  
  
  SELECT
      DATENAME(MONTH, PlantPollutions.Date) AS MONTH,
      SUM(PlantPollutions.BurnerNaturalGas + PlantPollutions.OilHeaterNaturalGas) AS THIS_MONTH,
      (
      SELECT
              SUM(SubPlantPollutions.BurnerNaturalGas + SubPlantPollutions.OilHeaterNaturalGas)
          FROM PlantPollutions AS SubPlantPollutions
          WHERE SubPlantPollutions.Date &gt;= DATEADD(MONTH, -11, PlantPollutions.Date)
          GROUP BY SubPlantPollutions.Date
      ) AS PREVIOUS_11_MONTHS
  FROM PlantPollutions
  
  WHERE PlantPollutions.PlantId = @plantId
    AND PlantPollutions.Date &gt;= @firstDayOfThisQuarter
  
  GROUP BY DATENAME(MONTH, PlantPollutions.Date)
  </code></pre>
  
  <p>When I comment out in the sub query <code>WHERE SubPlantPollutions.Date &gt;= DATEADD(MONTH, -11, PlantPollutions.Date) 
  GROUP BY SubPlantPollutions.Date</code> it works</p>