Calculate 2-Day Average and Show the Weekly Variances

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?

Hi billfusion

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

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

Select allOpen in new window

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

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.

Hi

The table name is: tbl_Postings

The date column is called: Posting_Date

The price column is called: Posting_Name

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

Select allOpen in new window

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

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

hi billfusion

My apologies!

I tried running your code and got the error below:

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.

hi

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

Thanks again

thanks so much for your assistance!

You're welcome and good luck.