Solved

SELECT AVG

Posted on 2011-03-17
22
356 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now