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

As of Date	October	November	December
9/3/2018	50	51	53
9/2/2018	49	50	52
9/1/2018	51	52	54

<tbody>
</tbody>

Off Peak Table

As of Date	October	November	December
9/3/2018	30	31	33
9/2/2018	29	30	32
9/1/2018	31	32	34

<tbody>
</tbody>

Averaged Table

As of Date	October	November	December
9/3/2018	(50*.51)+(30*.49)	(51*.51)+(31*.49)	(53*.51)+(33*.49)
9/2/2018	(49*.51)+(29*.49)	(50*.51)+(30*.49)	(52*.51)+(32*.49)
9/1/2018	(51*.51)+(31*.49)	(52*.51)+(32*.49)	(54*.51)+(34*.49)

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 349px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

(50*.51)+(30*.49)

<tbody>
</tbody>

</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).