Solved

SELECT AVG

Posted on 2011-03-17
22
364 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
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 142

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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