Learn how to a build a cloud-first strategyRegister Now

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

Calculate 2-Day Average and Show the Weekly Variances

Hello

I have a table that stores daily prices and I would now like to do calculate the 2-day average and show the weekly variances in a query.  

To explain, the query needs to:

1.  automatically pull the Wednesdays from the Pricing table

2.  calculate the average price of the product based on the prices over the previous 2 days (i.e. the Monday and the Tuesday)

2.  calculate the variance in these averages on a weekly basis (based on Wednesday's date).

I have attached a file in MS Excel that shows the calculations for the month of August 2012.

Hope that you can help!
Book2.xlsx
0
staceymoore
Asked:
staceymoore
  • 8
  • 7
1 Solution
 
billfusionCommented:
How many weeks do you need to include in step 2? Should the report reference Wednesday at all times or does it need to have any day as the reference point?
0
 
staceymooreAuthor Commented:
Hi billfusion

The query/report needs to reference the Wednesday at all times.
0
 
billfusionCommented:
This isn't all you need, but here is a start.  It uses pDate as Date field and Price as price field.

SELECT     tblWed.pdate, tblWed.wkday, tblWed.price, (tblTue.price + tblMon.price) / 2 AS TwoDayAve
FROM         (SELECT     pdate, price, DATENAME(weekDAY, pdate) AS wkday
                       FROM          test
                       WHERE      DATENAME(weekDAY, pdate) = 'Wednesday') tblWed INNER JOIN
                          (SELECT     pdate, price, DATENAME(weekDAY, pdate) AS wkday
                            FROM          test
                            WHERE      DATENAME(weekDAY, pdate) = 'Tuesday') tblTue ON tblWed.pdate = tblTue.pdate + 1 INNER JOIN
                          (SELECT     pdate, price, DATENAME(weekDAY, pdate) AS wkday
                            FROM          test
                            WHERE      DATENAME(weekDAY, pdate) = 'Monday') tblMon ON tblWed.pdate = tblMon.pdate + 2

Open in new window

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.

 
staceymooreAuthor Commented:
Hi

Thanks for your work!

My initial questions are:

1.  Are the prices for Tuedays and Mondays stored in separate tables (such as tblTue and tblMon)? All of the pricing data for all of the days are stored in 1 table.

2.  I also see that there is a field called "wkday".  Do you recommend that I insert a column in my pricing table called "Weekday" and populate it with the corresponding days of the week?

Thx
0
 
billfusionCommented:
tblTue and tblMon are not real tables.  You don't need to create them or even add a table for weekday.  tblTue, tblWed, and tblMon are views of your original price table.  Same for wkDay.  It is the result of running the function that returns the weekday from a date.  The query above works assuming that the table that contains the price is named 'test'.  Your dates are are in that table in column pDate and price is in that table in column Price.

If you can send me your table name, the the two column that have the date and price, I can fix the query to work for you.
0
 
staceymooreAuthor Commented:
Hi

The table name is: tbl_Postings

The date column is called: Posting_Date

The price column is called: Posting_Name
0
 
billfusionCommented:
Try this, it should run on MSSQL.  What SQL server are you running?
SELECT     tblWed.Posting_Date, tblWed.wkday, tblWed.Posting_Name, (tblTue.Posting_Name + tblMon.Posting_Name) / 2 AS TwoDayAve
FROM         (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                       FROM          tbl_Postings
                       WHERE      DATENAME(weekDAY, Posting_Date) = 'Wednesday') tblWed INNER JOIN
                          (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                            FROM          tbl_Postings
                            WHERE      DATENAME(weekDAY, Posting_Date) = 'Tuesday') tblTue ON tblWed.Posting_Date = tblTue.Posting_Date + 1 INNER JOIN
                          (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                            FROM          tbl_Postings
                            WHERE      DATENAME(weekDAY, Posting_Date) = 'Monday') tblMon ON tblWed.Posting_Date = tblMon.Posting_Date + 2

Open in new window

0
 
billfusionCommented:
Here is the full query for MSSQL, all you need is your existing tbl_postings table:

SELECT     tblWed.Posting_Date, tblWed.wkday, tblWed.Posting_Name, (tblTue.Posting_Name + tblMon.Posting_Name) / 2 AS TwoDayAve
FROM         (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                       FROM          tbl_Postings
                       WHERE      DATENAME(weekDAY, Posting_Date) = 'Wednesday') tblWed INNER JOIN
                          (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                            FROM          tbl_Postings
                            WHERE      DATENAME(weekDAY, Posting_Date) = 'Tuesday') tblTue ON tblWed.Posting_Date = tblTue.Posting_Date + 1 INNER JOIN
                          (SELECT     Posting_Date, Posting_Name, DATENAME(weekDAY, Posting_Date) AS wkday
                            FROM          tbl_Postings
                            WHERE      DATENAME(weekDAY, Posting_Date) = 'Monday') tblMon ON tblWed.Posting_Date = tblMon.Posting_Date + 2

Open in new window

0
 
staceymooreAuthor Commented:
Hello

Sorry that I took so long to get back to you on this!

Thanks for your work but I was able to develop another method that pulls the prices for the Monday and Tuesday using the DLOOKUP function in the query.

For example, if the base date is a Wednesday and I need to lookup Monday's price, I used:

Monday: DLookUp("[Price]","tbl_Prices","[Price_ID]=" & [Price_ID]-2)

This would work since the ID runs in sequential order from 1 to 366 with each ID/row representing a date.

All I need to figure out now is how to calculate/display the variance between the record and the previous record as per the attached.

Can you assist?
Book3.xlsx
0
 
billfusionCommented:
did you see the post that has the completed solution? it's dated 2012-09-20 at 16:57:47.
0
 
staceymooreAuthor Commented:
Error Messagehi billfusion

My apologies!

I tried running your code and got the error below:
0
 
billfusionCommented:
Hi staceymoore,

It looks like the query you ran is missing the 2 at the end of the last line.  Please try copying the code again fully and run it.
0
 
staceymooreAuthor Commented:
hi

thanks for the correction...your solution works perfectly!

Thanks again
0
 
staceymooreAuthor Commented:
thanks so much for your assistance!
0
 
billfusionCommented:
You're welcome and good luck.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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