Solved

Get the 20 day average of a integer field

Posted on 2015-01-15
Medium Priority
79 Views
I have a table with ID, BusDate and Volume as columns

I need to create another field 20dayAvgVolume.
This field should be populated with data taking into account the preceeding 19 plus current day and get the Average of the Volume field for all those days.

Please note I am using SQL server 2008
0
Question by:countrymeister
• 2
• 2

LVL 24

Expert Comment

ID: 40552198
Select ID, BusDate, Volume,
(Select Avg(Volume) from myTable as T where T.ID <= U.ID and T.ID >= U.ID - 19) as 20dayAvgVolume
From myTable as U
0

LVL 1

Author Comment

ID: 40552253
Hi ! Philip

ID is not sequential incremented by 1
0

LVL 24

Expert Comment

ID: 40552331
Then change the WHERE clause to

WHERE T.BusDate <= U.BusDate and T.BusDate >= dateadd(day,-19,U.BusDate)
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40552403
Is there only one entry per day?
If so, see if this gives the correct results:

SELECT tn1.BusDate, (tn1.Volume + data_last_19_days.volume_total) / (1 + data_last_19_days.row_count) AS [20dayAvgVolume] --or fewer days if less than 20 are available
FROM table_name tn1
CROSS APPLY (
SELECT SUM(Volume) AS volume_total, COUNT(*) AS row_count
FROM (
SELECT TOP (19) tn2.volume
FROM table_name tn2
WHERE tn2.BusDate < tn1.BusDate
ORDER BY tn2.BusDate DESC
) AS derived
) AS data_last_19_days

If so, we can convert SELECT into an UPDATE if needed to populate a column in the table.
0

LVL 1

Author Comment

ID: 40553391
Yes, there is only one entry per day
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.