Solved

Get the 20 day average of a integer field

Posted on 2015-01-15
5
69 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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