Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get the 20 day average of a integer field

Posted on 2015-01-15
5
Medium Priority
?
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

Accepted Solution

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

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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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