Need to calculate the average of cost based on a per fiscal week basis

Question

I hope someone can help with this issue I have, which is I am trying to work out a weekly average from the following data example:

    Practice ID      Cost          FiscalWeek
        1            10.00             1
        1            33.00             2
        1            55.00             3
        1            18.00             4
        1            36.00             5
        1            24.00             6
        13           56.00             1
        13           10.00             2
        13           24.00             3
        13           30.00             4
        13           20.00             5
        13           18.00             6

What I want is to group by the Practice ID but work out the average for each practice (there are over 500 of these not just those above) and work this out for each week so for example at Week 1 there will be no average, but Week 2 will be the average of Weeks 1 and 2, then Week 3 will be the average of Weeks 1, 2 and 3 and then so on. I need to then show this by Practice ID and for each Fiscal Week.

At the moment I have some code that is not pretty and there has to be an easier way, this code is:

I pass all the data into a table variable then using a CTE I then use case statements to set each individual week like:

  CASE WHEN fiscalweek = 1 THEN cost ELSE 0 END AS [1],
  CASE WHEN fiscalweek = 2 THEN cost ELSE 0 END AS [2],
  CASE WHEN fiscalweek = 3 THEN cost ELSE 0 END AS [3]

This would then bring back the week 1 cost and so on into it's own column e.g. 1, 2, 3 etc. , then I've used a second CTE to sum the columns for each week so for example to work out week 6 I would use this code:

    sum([1]) as 'Average Wk 1',
    sum([1]+[2])/2 as 'Average Wk 2',
    sum([1]+[2]+[3])/3 as 'Average Wk 3',
    sum([1]+[2]+[3]+[4])/4 as 'Average Wk 4',
    sum([1]+[2]+[3]+[4]+[5])/5 as 'Average Wk 5'
    sum([1]+[2]+[3]+[4]+[5]+[6])/6 as 'Average Wk 6'

I've thought about various different ways of working out this average accurately in T-SQL so I can then drop this into SSRS eventually. I've thought about using a While..Loop, Cursor but failing to see an easy way of doing this.

Answer 1

You are looking for the cumulative average of the averages. In databases that support window/analytic functions, you can do:

select fiscalweek, avg(cost) as avgcost,
       avg(avg(cost)) over (order by fiscalweek) as cumavg
from practices p
group by fiscalweek
order by 1;

If you don't have window functions, then you need to use some form of correlated subquery or join:

select p1.fiscalweek, avg(p1.avgcost)
from (select fiscalweek avg(cost) as avgcost
      from practices p
      group by fiscalweek
     ) p1 join
     (select fiscalweek avg(cost) as avgcost
      from practices p
      group by fiscalweek
     ) p2
     on p12 <= p1
group by p1.fiscalweek
order by 1;

I do want to caution you that you are calculating the "average of averages". This is different from the cumulative average, which could be calculated as:

select fiscalweek, 
      (sum(sum(cost)) over (order by fiscalweek) /
       sum(count(*)) over (order by fiscalweek)
      ) avgcost
from practices p
group by fiscalweek
order by 1;

One treats every week as one data point in the final average (what you seem to want). The other weights each week by the number of points during the week (the latter solution). These can produce very different results when weeks have different numbers of points.