Need suggestions on splitting a row in a table into 2 or more rows

[nganesha]

Hi,



I am working on a project that requires to calculate interest on the amount. There are 2 tables, 1 with Interest rate for a product for different and periods and another is product table with amounts in different period.

Table 1 (Rates table)

Product From To Rate
1111 1/1/2012 1/20/2012 .75
1111 1/20/2012 1/28/2012 .50
1111 1/28/2012 4/6/2012 .40

Table 2 (Product table)
Product From To Amount
1111 1/1/2012 1/17/2012 10000
1111 1/17/2012 1/24/2012 15000
1111 1/24/2012 2/25/2012 20000


What i need to do is to calculate interset based on the above 2 table and insert into another table (Interest). However, the problem is with identifying correct rates for the periods in Table 2.

Eg.
for period 1/1 to 1/17: applicable rate would be .75
for period 1/17 to 1/24: From 1/17 to 1/20 the rate would be .75 and from 1/20 to 1/24 it would be .50

this is what i want to achieve, basically to split the period between 1/17 to 1/24 into 2 so that appropriate rate can be applied.

Have not been able to visualize how to achieve this. Any help would be appreciated.

Thank you,
Navin

[June7]

I recommend writing a custom function to accomplish determining the correct rate(s) for each record and return the calculated value.

Overlapping To/From dates in data might cause issues.