Solved

Calculate 2-Day Average and Show the Weekly Variances

Posted on 2012-09-20
366 Views
Last Modified: 2012-09-30
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
Question by:staceymoore
15 Comments

LVL 6

Expert Comment

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

Author Comment

Hi billfusion

The query/report needs to reference the Wednesday at all times.
0

LVL 6

Expert Comment

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
``````
0

Author Comment

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

LVL 6

Expert Comment

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

Author Comment

Hi

The table name is: tbl_Postings

The date column is called: Posting_Date

The price column is called: Posting_Name
0

LVL 6

Expert Comment

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
``````
0

LVL 6

Accepted Solution

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
``````
0

Author Comment

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

LVL 6

Expert Comment

did you see the post that has the completed solution? it's dated 2012-09-20 at 16:57:47.
0

Author Comment

hi billfusion

My apologies!

I tried running your code and got the error below:
0

LVL 6

Expert Comment

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

Author Comment

hi

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

Thanks again
0

Author Closing Comment

thanks so much for your assistance!
0

LVL 6

Expert Comment

You're welcome and good luck.
0

Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

Featured Post

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!