Solved

Get the 20 day average of a integer field

Posted on 2015-01-15
5
68 Views
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
0
Comment
Question by:countrymeister
  • 2
  • 2
5 Comments
 
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
0
 
LVL 1

Author Comment

by:countrymeister
ID: 40552253
Hi ! Philip

ID is not sequential incremented by 1
0
 
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)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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

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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

808 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