Search code examples
sqlaggregate

Include Non-Existent Rows in Aggregate (Partition with Preceding and Following Rows)

I'm trying to get an average of the previous six months counts. However, I noticed if there are only 4 previous months, it will only do an average of those 4 months instead of 6 months. Is there a way to make so I forcefully sum over the 6 months?

select
      ST.AccountNumber
    , st.PrevMonth
    , st.[Transaction Effective Date]
    , st.[Transaction Amt]
    , st.CurrentMonthTransCnt
    , mt.EndOfMonth
    , AvgMonthlyTransCntLast6Months = AVG(isnull(cnt, 0)) OVER (PARTITION BY MT.AccountNumber order by rowid ROWS BETWEEN 1 following and 6 following)
--into #AvgCntAndStdDev
from EDWAnalytics.ML.TEMP_SymitarTransactionsFinal as ST
    left join MonthlyTransCnt as MT on MT.EndOfMonth = ST.PrevMonth and ST.AccountNumber = MT.AccountNumber
where ST.AccountNumber = '0000709510'
Solution

    1. If you want the average of the previous six months for each month then the order should be inverted: order by rowid desc
      Another option is to use the current order, but with ROWS BETWEEN 6 preceding and 1 preceding

    2. If you want the average to always be computed over six months you should replace avg with sum/6.

      isnull(sum(cnt) OVER (PARTITION BY MT.AccountNumber order by rowid ROWS BETWEEN 6 preceding and 1 preceding),0)/6