Search code examples
sql-serversql-server-2014

SQL Server Error: 'BETWEEN ... FOLLOWING AND CURRENT ROW' is not a valid window frame and cannot be used with the OVER clause.'?

I am trying to sum 3 months' net value with the one months' net values. The code

SELECT [CMONTH]
      ,[ID]
      ,[NET_VALUE_1M]
      ,SUM(CAST([NET_VALUE_1M] as float)) OVER 
          (PARTITION BY [ID] ORDER BY [CMONTH] 
             ROWS BETWEEN 3 FOLLOWING AND CURRENT ROW) as 'NET_VALUE_3M'   
  FROM [Channel_AGG]

is firing the error

Msg 4193, Level 16, State 4, Line 2
'BETWEEN ... FOLLOWING AND CURRENT ROW' is not a valid window frame 
and cannot be used with the OVER clause.

Why, what is causing this and how to solve it?

Solution

  • You need to use

    ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING

    The upper bound cannot be smaller than the lower bound.