Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

query to average weekly data

Experts,
I'm trying to create a date parameter query for a line chart that:
1) Based upon the date range create a line chart for each week ending on a Friday where it returns an average for the week. For example, a date range of 6/1/08 - 6/30/08 would return the following:
06/08/08      750
06/13/08      655
06/20/08      600
06/27/08      825
I started with this:

SELECT tblGrossStrokesPerHour.GSPHDate, tblGrossStrokesPerHour.GrossStrokesPerHour, tblGrossStrokesPerHour.LineCode
FROM tblGrossStrokesPerHour
WHERE (((tblGrossStrokesPerHour.GSPHDate) Between [Start Date] And [End Date]) AND ((tblGrossStrokesPerHour.LineCode)=8))
ORDER BY tblGrossStrokesPerHour.GSPHDate;

I attached the a database
 
gsph.zip
0
Frank Freese
Asked:
Frank Freese
1 Solution
 
FlysterCommented:
I didn't have time to try this on your DB. See if this works for you:

SELECT Avg(tblGrossStrokesPerHour.GrossStrokesPerHour) AS AvgOfGrossStrokesPerHour, tblGrossStrokesPerHour.LineCode, Format([GSPHDate],"ww",7) AS WeekNumber, Max([GSPHDate]) AS WeekEnding
FROM tblGrossStrokesPerHour
GROUP BY tblGrossStrokesPerHour.LineCode, Format([GSPHDate],"ww",7)
HAVING (((tblGrossStrokesPerHour.LineCode)=8) AND ((Max([GSPHDate])) Between [Start Date] And [End Date]));

Flyster
0
 
Frank FreeseAuthor Commented:
You nailed it! Many thanks!!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now