Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

Get the 20 day average of a integer field

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
countrymeister
Asked:
countrymeister
  • 2
  • 2
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
countrymeisterAuthor Commented:
Hi ! Philip

ID is not sequential incremented by 1
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then change the WHERE clause to

WHERE T.BusDate <= U.BusDate and T.BusDate >= dateadd(day,-19,U.BusDate)
0
 
Scott PletcherSenior DBACommented:
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
 
countrymeisterAuthor Commented:
Yes, there is only one entry per day
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now