Creating Query of Weighted Averaging Across Tables

[danpetersen2007]

Hi there i have multiple tables all in the below format only much more data about 2000 rows and 200 columns. Column A describes the day for which each following month (column) was priced.

I currently have all the data for the peak table and off peak table. I am looking to create the query to generate the averaged table. Any help would be great! thanks

Peak Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]As of Date[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]9/3/2018[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]9/2/2018[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD]54[/TD]
[/TR]
</tbody>[/TABLE]

Off Peak Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]As of Date[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]9/3/2018[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]9/2/2018[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]34[/TD]
[/TR]
</tbody>[/TABLE]

Averaged Table
[TABLE="width: 500"]
<tbody>[TR]
[TD]As of Date[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]9/3/2018[/TD]
[TD](50*.51)+(30*.49)[/TD]
[TD](51*.51)+(31*.49)[/TD]
[TD](53*.51)+(33*.49)[/TD]
[/TR]
[TR]
[TD]9/2/2018[/TD]
[TD](49*.51)+(29*.49)[/TD]
[TD](50*.51)+(30*.49)[/TD]
[TD](52*.51)+(32*.49)[/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD](51*.51)+(31*.49)[/TD]
[TD](52*.51)+(32*.49)[/TD]
[TD](54*.51)+(34*.49)[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 349px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD](50*.51)+(30*.49)[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[alansidman]

I believe that this can be accomplished with a lot of heavy lifting because your tables are not normalized. If I were to have built this database, then I would have set up a table as follows

TableX
------------
Record ID (PK)
AsOfDate
Peak (Y/N)
Month
Value

With your data in this manner, you can select any period, Peak or Off Peak or both, and run a Group By Query that will allow you to slice and Dice as you need.

Data Table Normalization is the basis of a good RDBMS.

You may want to read this white paper to more clearly understand database design.

http://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html

 

[xenou]

Just out of curiousity where do your weights of .51 and .49 come from, and what are the other 196 columns (since I quickly run out of months if I start filling in Jan-Sep).