title: Calculate past 3 month average for every past 3rd month
body: <p>I am using SQL Server 2014. I have a table like this </p>
  
  <pre><code>   create table revenue (id varchar(2), trasdate date, revenue int);
     insert into revenue(id, trasdate, revenue)
     values ('aa', '2018/09/01', 1234.5),
     ('aa' , '2018/08/04', 450),
     ('aa', '2018/07/03',500),
     ('aa', '2018/06/04',600),
    ('ab', '2018/09/01', 1234.5),
    ('ab' , '2018/08/04', 450),
      ('ab', '2018/07/03',500),
      ('ab', '2018/06/04',600),
     ('ab', '2018/05/03', 200),
     ('ab', '2018/04/02', 150),
    ('ab', '2018/03/01', 350),
    ('ab', '2018/02/05', 700),
    ('aa', '2018/01/07', 400)
  ;
  </code></pre>
  
  <p>I am preparing a SQL query to create a SSRS report. I want to calculate a past 3 month average for current and every past 3rd month with result like below. As we are in month of September right now. The result should show something like this:</p>
  
  <pre><code>**id    Period  Revenue_3Mon**
  aa  March-May   233
  aa  June-Aug    516
  ab  March-May   233
  ab  June-Aug    516
  </code></pre>
  
  <p>Though I can figure out about the Period column. I was mainly focussing on getting the Revenue_3Mon. So I initially tried with the below query after some googling. But this query throws an error as incorrect syntax near 'rows' and if I remove rows from the query then it throws an error as Incorrect syntax near the keyword 'between'. And incorrect syntax near i.</p>
  
  <pre><code>select i.id,i.mon,
     avg([i.mon_revenue]) over (partition by i.id, i.mon order by [i.id], 
    [i.mon] rows between 3  preceding and 1 preceding row) as revenue_3mon -- 
  --  using 3 preceding and 1 preceding row you exclude the current row
   from (select a.id, month(a.trasdate) as mon,
           sum(a.revenue) as mon_revenue
    from revenue a
    group by a.id, month(a.trasdate)) i
   group by i.id, i.mon
   order by i.id,i.mon;
  </code></pre>
  
  <p>After few efforts, I gave up on this query and came up with new solution which was a bit close to my expectation (after lots of trial and errors). </p>
  
  <pre><code>Declare @count as int;
  declare @max as int;
  set @count = 4
  declare @temp as table (id varchar(2), monthoftrasdate int, revenue int, 
  [3monavg] int);
  SET @MAX = (SELECT distinct MAX(a.ROWNUM) FROM (SELECT id, month(trasdate) 
   as mon, SUM(revenue) TotalRevenue,
         -- sum(revenue) as mon_revenue,
         ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MONTH(TRASDATE)) AS ROWNUM
          FROM revenue
         GROUP BY ID, MONTH(TRASDATE)         
          ) A GROUP BY A.ID);
  
       while (@count &lt;= @max )
      begin
  
  WITH CTE AS (
  SELECT id, month(trasdate) as mon, SUM(revenue) TotalRevenue,
         -- sum(revenue) as mon_revenue,
          ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MONTH(TRASDATE)) AS 
  ROWNUM
  FROM revenue
  GROUP BY ID, MONTH(TRASDATE)
   )
  
    insert into @temp
    SELECT A.ID,A.MON, a.TotalRevenue
      ,( SELECT avg(b.TotalRevenue) as avgrev
      FROM CTE B
      WHERE B.ROWNUM BETWEEN  A.ROWNUM-3 AND A.ROWNUM-1
      AND A.ID = B.ID --AND A.mon = B.mon
      --and b.ROWNUM &lt; a.ROWNUM
      and (a.mon &gt; 3 and a.ROWNUM &gt; 3)
      GROUP BY B.id
  
      ) AS REVENUE_3MON
    FROM CTE A
  
   set @count = @count + 1
   end
  
   select distinct a.* from @temp a
  </code></pre>
  
  <p>The reason I had to use 'distinct' is because the query was showing duplicate records for every id and every month. So far the result shows like below</p>
  
  <pre><code>id  MonthofTrasdate Revenue 3MonAvg
  aa  1                400    NULL
  aa  2                700    NULL
  aa  3                350    NULL
  aa  4                150    483
  aa  5                200    400
  aa  6                600    233
  aa  7                500    316
  aa  8                450    433
  aa  9               1234    516
  ab  1                400    NULL
  ab  2                700    NULL
  ab  3                350    NULL
  ab  4                150    483
  ab  5                200    400
  ab  6                600    233
  ab  7                500    316
  ab  8                450    433
  ab  9               1234    516
  </code></pre>
  
  <p>This pulls out past 3 month average for every month. But i will just manipulate the rest on SSRS the way i want it.</p>
  
  <p>As currently my table has no data for previous year. This works for me showing the appropriate result for next couple of months for now. But my concern is when I have to show my boss for next year Jan, Feb and March then it should be able to pull also for these months as well like Oct-Dec (Previous year), Nov-Jan and Dec - Feb. I am struggling to figure out the proper way to put this in my query. </p>
  
  <p>Can you please help me out with this query? And also let me know what is wrong with my former query.</p>