Search code examples
sqlsql-serverdata-partitioning

Calculate average in SQL based on condition for end date and number of records

I want to calculate average value for records looking similarly like the ones in the table below (this is just an example, values does not matter):

enter image description here

I have query looking like this:

SELECT Id,
AVG(Value) OVER(partition by ID Order by [date] rows between 2 preceding and current row) as avg_value,
FROM [table]
WHERE Code = 'SLS' --- there are more codes usually

The conditions I want to add but cannot decide how to do so properly are:

  1. Calculate average only when the maximum Date for each Id is >= from current year minus 1
  2. Calculate average only when I have values for the number of years I specify which in this case is 3.

Both conditions must be met. In this case Id = 2 does not meet even one of the conditions.

Solution

  • I came to a solution which seems to be working:

    SELECT Id,
AVG(Value) OVER(partition by ID Order by [date] rows between 2 preceding and current row) as avg_value,
FROM (
      SELECT *, 
             Max([Date]) OVER(partition by Id, Code) as MaxDate, 
             COUNT(*) OVER(partition by Id, Code) as RecordsPerCode
      FROM [table]
      WHERE Code = 'SLS'
  ) a
  WHERE 1=1 
  AND YEAR(MaxDate) = YEAR(DATEADD(year, -1, GETDATE()))
  AND RecordsPerCode >= 3

    Any suggestions or advice will be appreciated.