[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

SELECT AVG

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
aka_FATCAT
Asked:
aka_FATCAT
  • 11
  • 7
  • 3
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
aka_FATCATAuthor Commented:
Getting the same error:

Incorrect syntax near ')'.
0
 
Pratima PharandeCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
aka_FATCATAuthor Commented:
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
 
Pratima PharandeCommented:
is this working

SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 
aka_FATCATAuthor Commented:
Yup.
0
 
Pratima PharandeCommented:
is this working seperatly

SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC
0
 
aka_FATCATAuthor Commented:
The multi-part identifier "c.date" could not be bound.
0
 
Pratima PharandeCommented:
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
 
Pratima PharandeCommented:
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
 
aka_FATCATAuthor Commented:
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
 
nirmalrampkCommented:
What is your exact requirement, can you ellabrate your requirement
0
 
aka_FATCATAuthor Commented:
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
 
Pratima PharandeCommented:
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
 
aka_FATCATAuthor Commented:
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
 
aka_FATCATAuthor Commented:
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
 
Pratima PharandeCommented:
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
 
aka_FATCATAuthor Commented:
Working chart.
aka-FATCAT-429747.flv
0
 
nirmalrampkCommented:
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
 
aka_FATCATAuthor Commented:
You're actually right. So now what do I do?
0
 
nirmalrampkCommented:
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
 
aka_FATCATAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now