Get the 20 day average of a integer field

Posted on 2015-01-15
Medium Priority
Last Modified: 2015-01-30
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
Question by:countrymeister
  • 2
  • 2
LVL 24

Expert Comment

by:Phillip Burton
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

Author Comment

ID: 40552253
Hi ! Philip

ID is not sequential incremented by 1
LVL 24

Expert Comment

by:Phillip Burton
ID: 40552331
Then change the WHERE clause to

WHERE T.BusDate <= U.BusDate and T.BusDate >= dateadd(day,-19,U.BusDate)
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
    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.

Author Comment

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question