Geek asks and answers

You need to calculate the average cost based on each fiscal week.

I hope someone can help with this problem, I have that 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

I want to group using the practice identifier, but develop an average value for each practice (more than 500 of them are not only higher) and work every week this week, for example, on week 1 there will not be an average, but week 2 will be on average for weeks 1 and 2, then week 3 will be average for weeks 1, 2 and 3, and then and so on. I need to show this by practice ID and each fiscal week.

At the moment, I have some code that is not very good, and there should be an easier way, this code:

, CTE, case :

  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]

1 .. , . 1, 2, 3 .., CTE , , , 6. :

    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'

T-SQL, SSRS. While..Loop, Cursor, .

+3
3

. , / , :

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

, :

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;

, " ". , :

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;

( ). ( ). , .

+2

, :

SELECT t1.pid,
t1.fiscalweek,
(
  SELECT SUM(t.cost)/COUNT(t.cost)
  FROM tablename AS t
  WHERE t.pid = t1.pid
  AND t.fiscalweek <= t1.fiscalweek
) AS average
FROM tablename AS t1
GROUP BY t1.pid, t1.fiscalweek

,

SELECT SUM(t.cost)/COUNT(t.cost)


SELECT SUM(t.cost)/t1.fiscalweek

1-

SELECT SUM(t.cost)/(t1.fiscalweek - MIN(t.fiscalweek) + 1)

.

( № 1), .

, , , , .

0

I dont know. If I fully understand the question: But try doing this: should help you:

create table #practice(PID int,cost decimal,Fweek int)
insert into #practice values (1,10,1)
insert into #practice values (1,33,2)
insert into #practice values (1,55,3)
insert into #practice values (1,18,4)
insert into #practice values (1,36,5)
insert into #practice values (1,24,6)
insert into #practice values (13,56,1)
insert into #practice values (13,10,2)
insert into #practice values (13,24,3)
insert into #practice values (13,30,4)
insert into #practice values (13,20,5)
insert into #practice values (13,18,6)

select * from #practice
select pid,Cost,
(select AVG(cost) from #practice p2 where p2.Fweek <= p1.Fweek and p1.pid = p2.pid) WeeklyAVG,
Fweek,AVG(COST) over (Partition by PID)  as PIDAVG
from #practice p1;
0
source

More articles:


All Articles