Moving averages not calculating by group

Question

Post reply

Moving averages not calculating by group

DavidL

Hall of Fame

Points: 3582
More actions
October 23, 2006 at 12:03 pm

#170128

Hi all: I have a partially working moving average query. Sampe data is below:
ID BOL ReceiveDate extcdb Silo
504 18500 2006-09-04 00:00:00.000 80.50 26
505 18501 2006-09-04 00:00:00.000 80.80 26
505 18501 2006-09-04 00:00:00.000 80.80 25
506 18502 2006-09-04 00:00:00.000 80.90 25
511 18503 2006-09-07 00:00:00.000 80.60 25
516 18528 2006-09-11 00:00:00.000 80.80 25
517 18529 2006-09-11 00:00:00.000 80.80 25
523 18530 2006-09-14 00:00:00.000 80.60 25
525 18532 2006-09-14 00:00:00.000 81.30 25
524 18531 2006-09-14 00:00:00.000 80.80 25
530 18553 2006-09-18 00:00:00.000 81.00 26
529 18552 2006-09-18 00:00:00.000 81.10 26
528 18551 2006-09-18 00:00:00.000 80.50 26
538 18554 2006-09-21 00:00:00.000 80.90 26
539 18555 2006-09-21 00:00:00.000 80.50 26
540 18556 2006-09-21 00:00:00.000 80.90 26
548 18572 2006-09-25 00:00:00.000 80.20 25
550 18574 2006-09-25 00:00:00.000 80.40 25
561 18596 2006-10-02 00:00:00.000 80.60 26
562 18597 2006-10-02 00:00:00.000 81.00 26
560 18595 2006-10-02 00:00:00.000 81.30 26
566 18598 2006-10-05 00:00:00.000 81.70 26
This is about two month's of data for two silos, but I have many more months and many more silos. I would like to calculate a moving average of, say, the past 10 days of [extcdb] for each silo. I have the following query:
SELECT f.Silo
,MovingAvg=AVG(1.0*g.extcdb)
,f.receivedate
FROM vwKPIMaltSiloExt f
CROSS JOIN vwKPIMaltSiloExt g
WHERE (f.receivedate BETWEEN DATEADD(dd,-10,f.receiveDate) and f.receivedate)
GROUP BY f.Silo ,f.receivedate
ORDER BY f.Silo ,f.receivedate
That results in:
Silo Avg ReceiveDate
25 80.87 2006-09-04 00:00:00.000
25 80.87 2006-09-07 00:00:00.000
25 80.87 2006-09-11 00:00:00.000
25 80.87 2006-09-14 00:00:00.000
25 80.87 2006-09-25 00:00:00.000
26 80.87 2006-09-04 00:00:00.000
26 80.87 2006-09-18 00:00:00.000
26 80.87 2006-09-21 00:00:00.000
26 80.87 2006-10-02 00:00:00.000
26 80.87 2006-10-05 00:00:00.000
As you can see, it is calculating the average only once, for the entire dataset, not by silo and for each rolling time period. Anybody see something I have missed. Many thanks. D. Lewis

PS, I cannot rely on either the BOL or ID field as 'counter' so to speak. I must use the receive date to calculate the sliding window.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DavidL Hall of Fame Points: 3582 More actions · Answer 1

I tried rewriting it as a subquery, and that seems to work fine. Thanks anyway.

SELECT f.Silo

,f.receivedate

,(SELECT CONVERT(numeric(4,2),AVG(1.0*g.extcdb))

FROM vwKPIMaltSiloExt g

WHERE g.Silo=f.Silo

AND g.receivedate BETWEEN DATEADD(dd,-10,f.receiveDate) and f.receivedate

&nbsp AS MovingAvg

FROM vwKPIMaltSiloExt f

GROUP BY f.Silo,f.receivedate

ORDER BY f.Silo,f.receivedate

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I see difference in 2 places:

1) second option uses implicit INNER JOIN, not CROSS JOIN as first one;

2) compare these:

(f.receivedate BETWEEN DATEADD(dd,-10,f.receiveDate) and f.receivedate)

and

g.receivedate BETWEEN DATEADD(dd,-10,f.receiveDate) and f.receivedate

_____________
Code for TallyGenerator

DavidL Hall of Fame Points: 3582 More actions · Answer 3

Sergiy: RE comment #2: Very good! I hadn't spotted that. Thanks very much. I will now compare the results of the two queries to see if they are actually identical. D. Lewis