0

Take an example I have the following transaction table, with transaction values of each department for each trimester.

    TransactionID  | Department | Trimester | Year | Value | Moving Avg
    1              | Dep1       |     T1    | 2014 |   13  |
    2              | Dep1       |     T1    | 2014 |   43  |
    3              | Dep1       |     T2    | 2014 |   36  |

   300             | Dep1            T1 |    2017 |   28  |
   301             | Dep2            T1 |    2014 |   24  |

I would like to calculate moving average for each transaction from the same department, taking the window as from the 6 trimesters to 2 trimesters before the current line's trimester. Example for transaction 300 in T1 2017, I'd like to have the average of transaction values for Dep1 from T1-2015 to T2-2016.

How can I achieve this with sliding window function in SQL Server 2014. My thought is that I should use something like

SELECT 
AVG(VALUES) OVER 
(PARTITION BY DEPARTMENT ORDER BY TRIMESTER, 
YEAR RANGE [Take the range from previous 6 to 2 trimesters])

How would we define the RANGE clause. I suppose I could not use ROWS due to the number of rows for the window is unknown.

The same question for median. How would we rewrite for calculating the median instead of mean ?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Kenny
  • 1,902
  • 6
  • 32
  • 61
  • please add expected result in formatted text. Also show us your current query attempt – TheGameiswar Apr 12 '17 at 12:56
  • 2
    may be useful? http://stackoverflow.com/questions/43345402/sql-server-how-to-do-3-months-partition/43346202#43346202 – etsa Apr 12 '17 at 12:58

0 Answers0