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
staceymooreAsked:
Who is Participating?
 
billfusionConnect With a Mentor Commented:
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.