I am trying to calculate a weighted average commodity price for each of my customers who have various billing periods.
I have my customer data in 'CustomerData' table and daily commodity pricing in 'TotalConsumption' table. I am trying to calculate the average commodity price ('TotalDailyPrice'/'TotalDailyConsumption') for each customer based on their particular billing period. Customer's 'StartDate' and 'EndDate' for their billing period is in the 'CustomerData' table, but the daily consumption data is in the 'TotalConsumption' table and the two are not related.
I am trying to do this calculation in a query because I need this calculated value for further calculations that will ultimately end up on a report. I have tried DSum and have spent many hours on this site but have not had any luck. Please help...I'm getting desperate and running out of time!
I have my customer data in 'CustomerData' table and daily commodity pricing in 'TotalConsumption' table. I am trying to calculate the average commodity price ('TotalDailyPrice'/'TotalDailyConsumption') for each customer based on their particular billing period. Customer's 'StartDate' and 'EndDate' for their billing period is in the 'CustomerData' table, but the daily consumption data is in the 'TotalConsumption' table and the two are not related.
I am trying to do this calculation in a query because I need this calculated value for further calculations that will ultimately end up on a report. I have tried DSum and have spent many hours on this site but have not had any luck. Please help...I'm getting desperate and running out of time!
