Dear Red:
Although you say the PERIOD is actual days, the dates given in your
sample do not agree with that. 11/15/04 to 5/14/05 is 180 days for
your 6M. However, since the MARKET RATES table contains both
beginning and ending dates, you could use those dates and ignore the
PERIOD column. That may be more satisfactory, and a bit easier to do.
As I see it, the challenge is to obtain the two rows from PERIOD
needed for the proration. That would be the row whose END_DT falls
just before the END_DT of the CLIENT OPERATIONS row, and the one that
falls just after that. If you have the values from those two rows of
PERIOD, then the proration can be done easily.
Now, before jumping in to do this, there are possible exceptions to be
handled. Due to errors in data entry, it might be that the two rows
you want from the PERIOD table may not both exist. Also, as stated
before, it may be that you could fall right on one of the END_DT
values. Let's come back and handle those later.
SELECT C.*, P1.END_DT End1, P1.MarketRate Rate1,
P2.END_DT End2, P2.MarketRate Rate2
FROM [CLIENT OPERATIONS] C
INNER JOIN PERIOD P1 ON P1.ST_DT = C.ST_DT
INNER JOIN PERIOD P2 ON P2.ST_DT = C.ST_DT
ORDER BY C.ST_DT
This is just a starting point. If we can just limit the two instance
of PERIOD to one row each, we should be on our way.
SELECT C.*, P1.END_DT End1, P1.MarketRate Rate1,
P2.END_DT End2, P2.MarketRate Rate2
FROM [CLIENT OPERATIONS] C
INNER JOIN PERIOD P1 ON P1.ST_DT = C.ST_DT
INNER JOIN PERIOD P2 ON P2.ST_DT = C.ST_DT
WHERE P1.END_DT = (SELECT MAX(END_DT) FROM PERIOD P3
WHERE P3.ST_DT = C.ST_DT AND P3.END_DT <= C.END_DT)
AND P2.END_DT = (SELECT MIN(END_DT) FROM PERIOD P3
WHERE P3.ST_DT = C.ST_DT AND P3.END_DT >= C.END_DT)
ORDER BY C.ST_DT
Before proceeding, it would be useful to know if this works. The
objective is to show the one pair of PERIOD dates and rates that
bracket the desired END_DT for the CLIENT OPERATION shown.
If this works, prorating from the above should be simple.
Note that, if there is a missing PERIOD row then there may not be a
pair of PERIOD rows that bracket (as already discussed as an
exception). This will cause there to be no results for the associated
CLIENT OPERATIONS row. It will just drop out. A separate query may
be prepared to show those exceptions.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Dear Tom
Thanks for your comments
I think you put the problem in the right perspective...
Indeed the PERIOD column is "coded" (I don't know how...just received the
file like that..), and I regret to say that M is actual calendar days (in the
example I put a 30 day Month to make easier...).
You're right, if we land on a PERIOD there is no need for prorate (i.e., an
operation where END_DT and ST_DT of Table1 and Table 2 are the same)
My problem is to calculate the proration...
Thanks for your help
Red
:
Dear Red:
After studying this some, I'm beginning to make some guesses about
what you mean.
The PERIOD column seems to have some "coded" stuff in it that makes
this difficult. What is needed is to quantify those values, probably
in terms of a number of days. Alternately, it could be quantified in
days or months, using a variable number of days according to the
length of months in the calendar.
I suggest that PERIOD column contains 2 separate attributes here. One
is a number of units, the other is the unit to be used. This is a
small error in design, as you should not combine two separate
attributes in one column.
Anyway, we need a way to convert each of these, probably so they can
be used in the DateAdd function.
No, on to finding out which MARKET RATES rows are to be used. I'll
assume for the moment the following equivalence for the PERIOD values:
1D 1
1W 7
2W 14
1M 30
2M 60
3M 90
6M 180
9M 270
The task would be to find two of the above that bracket the actual
number of days found in the DateDiff of the ST_DT and END_DT and then
prorate. If you land right on a PERIOD then there's no need to
prorate.
The proration should not be difficult. The two MARKET RATE rows would
be determined using a pair of subqueries.
I'll stop now and wait to see how you respond to what I've asked so
far. Be sure to commend on whether M (month) is 30 days or actual
calendar months. If the latter, things are going to be a bit
trickier.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Mon, 15 Nov 2004 10:15:06 -0800, Red
Dear Tom
I'll try. Thanks in advance
Table 1 - CLIENT OPERATIONS
DESC AMOUNT ST_DT END_DT CUST_RATE MARKET_RATE
Cust A 1 000 11-15-04 12-20-04 2.00 ?
Cust B 2 000 11-15-04 1-19-05 2.50 ?
Cust C 3 000 11-15-04 3-20-05 3 ?
Table 1 - MARKET RATES
ST_DT END_DT PERIOD MARKET_RATE
11-15-04 11-16-04 1D 1.50
11-15-04 11-22-04 1W 1.50
11-15-04 11-30-04 2W 1.50
11-15-04 12-15-04 1M 1.75
11-15-04 1-14-05 2M 2.00
11-15-04 2-13-05 3M 3.00
11-15-04 5-14-05 6M 3.50
11-15-04 8-12-05 9M 4.00
11-15-04 11-10-05 1Y 4.50
What I need to calculate is the MARKET_RATE in table 1, which is the
interpolated rate for the period of table 2, e.g., knowing that client A
operation period is 35 days (END_DT- ST_DT, which is Nov 15- Dec20 in table
1), what is the interpolated MARKET_RATE from table 2 (only have a 3o
days(1M) rate and a 60 days(2M) rate? )?
Answer the MARKET RATE in table 1 for a 35 days (cust A) should be
interpolated (linear) from the 1M and 2 M buckets,in table 2 (MARKET RATES),
i.e. Market_Rate in table 1(35 days)= Market_Rate(30
days).[table2]+[(Market_Rate(60 days).[table2]-Market_Rate(30
days).[table2])/((1-14-05).[table2]-(12-15-04).[table2])*((12-20-04).[table1]-(12-15-04).[table2]]= 1.792
The query should calculate the Market rate for each client as above and
update the field MARKET_RATE in table 1 with the correct figures.
Looks easy if table 1 only have 3 operations, but the problem is that table
1 has nearly 3000 clients/operations.
Thanks
:
Dear Red:
This helps, but I need you to specify which columns in which tables
contain the information. Please give a complete example with source
data, calculations, and final answer, so I can see exactly what is to
be done and be sure to understand it. I will then attempt to write a
query that does what you want.
Sorry to seem so dense, but it is not always easy to get the specifics
of what you're doing from what we write here.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 12 Nov 2004 08:07:08 -0800, Red
I want to perform a linear interpolation.
The table 1 (client operations) would be provide the needed information to
start the process, e.g., client A has an operation starting today Nov, 12
(start_date) with a 13 months maturity (maturity_date) with a Client_rate for
this operation (for instance 3%). Table 2 (start date, end_date, period
(start-end), rate) would have the todays market rates for 12 months (3%) and
24 months (4%).
The query should deliver for each start_date in table 1 a matching start
date in table 2, and an interpolated market rate for each customer operation
based on the 12 month and 24 months rates in table 2.
The idea is to calculate for each customer the spread earned
(Market_rate-Client_rate) .
Thanks in advance
:
Dear Red:
Do you want to perform a simple, linear interpolation, or something
more complex, involvinb more than two points and some curve fitting?
From where do you get the "target date" for the interpolation? Is it
simply today's date, some date the user chooses, or some date found in
the tables?
Please describe the process you would use to do this manually. Then
review which columns and tables would be used to obtain the data to be
used in the calculation.
From this information, a query can likely be created. I'd be glad to
help if you'll provide more details.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 12 Nov 2004 03:17:02 -0800, Red
I have 2 tables:
-table 1 with some client operations, with the following information:
start_date; maturity_date; rate; amount;Client_number.
-table 2: historical market rates (since 2003) for the 1M, 3M, 6M upt to
30Years
What I would like to do is to calculate for each client operation the
appropriate market rate for the the period (interpolation of dates/rates
would have to be considered)
Any thoughts?
