Compare Different Rates

  • Thread starter Thread starter Molasses26
  • Start date Start date
M

Molasses26

I would like to create a report that applies the most current rate from each
rate table to each row of the monthly input data so that I can compare the
total to see which rate would be more beneficial for the customer. I don't
need to see all the calculations - just the total for each rate each month.
I just can't seem to figure out where to start with this. Any assistance
will be greatly appreciated!

I have a data table where I input the following fields:
Date Total On-Peak Off-peak Days
9/24/09 1990 746 1244 30
8/25/09 2117 777 1340 28
7/28/09 2481 884 1597 29

Then I have several rate tables:
RateTable1 RateTable2
EffDate TotRt SvChg EffDate OnRt OffRt SvChg
7/1/09 .04577 .056 7/1/09 .09666 .01746 .211
1/1/09 .0513 .052 1/1/09 .09023 .01657 .211

Calculations:
Rate1 (Total * TotRt) + (SvChg*Days)
9/24/09 (1990*.04577) + (.56 * 30) = 92.7623
8/25/09 (2117*.04577) + (.56 * 28) = 98.46309
7/28/09 (2481*.04577) + (.56 * 29) = 115.1794

Rate2 (OnPk*OnRt) + (OffPk*OffRt) + (SvChg*Days)
9/24/09 (746*.09666) + (1244*.01746) + (.211*30) = 100.11384
8/25/09 (777*.09666) + (1340*.01746) + (.211*28) = 104.3626
7/28/09 (884*.09666) + (1597*.01746) + (.211*29) = 115.1794

Desired report result:
Date Rate1 Rate2
9/24/09 92.7623 100.11384
8/25/09 98.46309 104.3626
7/28/09 115.1794 119.39702
 
Use one rate table like this --
EffDate TotRt OnRt OffRt SvChg1 SvChg2
7/1/2009 0.04577 0.09666 0.01746 0.056 0.211
1/1/2009 0.0513 0.09023 0.01657 0.052 0.211

The first query select the correct rate for the date.
MolassesDataRate --
SELECT MolassesData.Date, Max(MolassesRate.[EffDate]) AS MaxOfEffDate
FROM MolassesData, MolassesRate
WHERE (((MolassesRate.EffDate)<=[MolassesData].[Date]))
GROUP BY MolassesData.Date;

SELECT MolassesData.Date, ([Total]*[TotRt])+([SvChg1]*[Days]) AS Rate1,
([On-Peak]*[OnRt])+([Off-Peak]*[OffRt])+([SvChg2]*[Days]) AS Rate2
FROM (MolassesDataRate INNER JOIN MolassesData ON MolassesDataRate.Date =
MolassesData.Date) INNER JOIN MolassesRate ON MolassesDataRate.MaxOfEffDate =
MolassesRate.EffDate
ORDER BY MolassesData.Date;
 
Back
Top