Solved

SELECT AVG

Posted on 2011-03-17
22
375 Views
Last Modified: 2012-05-11
How can I get this code to work?
SELECT eod, AVG(SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date) as avgeod, CONVERT(char(10), Date, 101) AS FormatedDate
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
Comment
Question by:aka_FATCAT
[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
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35154222
first, you need to clarify what exactly you want as output, based on the input ....
the formula indeed won't work, syntax might be like below.
check out that the subquery shall have a ORDER BY, otherwise the TOP will not result in consistent results, eventually
SELECT eod
, CONVERT(char(10), Date, 101) AS FormatedDate
, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeodFROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35154293
Getting the same error:

Incorrect syntax near ')'.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154305
SELECT eod
, CONVERT(char(10), Date, 101) AS FormatedDate
, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeod FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:aka_FATCAT
ID: 35154312
Getting the same error:

Incorrect syntax near ')'.
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154335
is this working

SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35154358
Yup.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154361
is this working seperatly

SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC
0
 

Author Comment

by:aka_FATCAT
ID: 35154363
The multi-part identifier "c.date" could not be bound.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154366
try this
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) X )
as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154379
try this
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf ORDER BY cf.date DESC ) X )
as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35154423
This is close, but its only right the first four records, after that it just repeats the last number.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc

Open in new window

0
 

Expert Comment

by:nirmalrampk
ID: 35154437
What is your exact requirement, can you ellabrate your requirement
0
 

Author Comment

by:aka_FATCAT
ID: 35154720
I have an amount entered daily. (eod)
I would like to calculate the moving average based on the last month (4 weeks).

So I call on the db to return all the entries on a given weekday (i.e. Saturday)

Then I ask for the previous 4 entries on (i.e. Saturday) and avg that data.

After which I would like it to look at the next entry and compare that to the previous 4 (i.e. Saturdays), based on that entries date.

table = cash_flow
column1 = eod
column2 = date

Basically I'm looking for a 4 week moving average based on the day of the week.

The code is working, but only for the first four entries.

Here are the results of the current query.

eod                      FormatedDate         avgeod
11843.3900      09/11/2010      11843.3900
9180.1900      09/18/2010      10511.7900
9171.2500      09/25/2010      10064.9433
10055.7700      10/02/2010      10062.6500
11632.9900      10/09/2010      10062.6500
11233.7900      10/16/2010      10062.6500
10403.0200      10/23/2010      10062.6500
13332.5300      10/30/2010      10062.6500
11370.6000      11/06/2010      10062.6500
13590.4400      11/13/2010      10062.6500
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc

Open in new window

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35154811
remove top 4

try this

 SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc
0
 

Author Comment

by:aka_FATCAT
ID: 35154877
It throws the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
0
 

Author Comment

by:aka_FATCAT
ID: 35154913
I removed the first ORDER BY and it worked perfectly.

Thank you.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 5) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 5
ORDER BY date asc

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154932
try this

SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT
Top( select count(*) From cash_flow ct where ct.date <= c.Date AND DATEPART(WEEKDAY, date) = 7)
 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc
0
 

Author Comment

by:aka_FATCAT
ID: 35154946
Working chart.
aka-FATCAT-429747.flv
0
 

Expert Comment

by:nirmalrampk
ID: 35154988
hi,
 If you requirement is
 "I would like to calculate the moving average based on the last month (4 weeks)"

previous query is not correct, becuase it will calculate average for all previous values not last month (last 4 weeks)

0
 

Author Comment

by:aka_FATCAT
ID: 35155021
You're actually right. So now what do I do?
0
 

Expert Comment

by:nirmalrampk
ID: 35155190
Hi,
 Try this

this query will return all Sunday with previous four Sundays avg.

SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate
      ,(SELECT avg(ct.eod)  FROM cash_flow ct
            where ct.date > dateadd(d,-4*7,c.Date)
                  AND ct.date < c.Date
                  AND DATEPART(WEEKDAY, date) = 7)
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35162378
This code seems to be working just as I wanted.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM
 ( SELECT eod from cash_flow cf
     where cf.date <= c.Date
     AND DATEPART(WEEKDAY, date) = 1
     AND (c.Date - cf.date) < 28
  ) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 1
ORDER BY date asc

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 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