Need help writing query for min lease payments

[Guest]

Hi,

I’m stumped on how to approach writing a query to calculate minimum lease
payments where the leases have payment escalations. I have two tables: a
lease table and a payment escalation table. The payment table has start and
end dates for each payment amount.

For illustration, Lease A started 1/1/2006 and ends 12/31/2010 with a
payment of $100 month increasing by 10% each year. Lease B started 6/1/2006
and ends 5/31/2007 with a payment of $200 month increasing 10% each year.

(Please note I do not need to calculate the escalated payment amounts – the
table is already populated with this data)

What I need to do is determine the minimum annual lease payment for the next
five years from a desired point in time. If the desired point in time is
9/1/2006, then Year 1 = 9/1/2006 to 8/31/2007; Year 2 = 9/1/2007 to
8/31/2008; and so forth.

Using the leases above, Year 1 payment would be:
Lease A: [4 months * 100] + [8 months *(100*110%)]
Lease B: [9 months * 200] + [3 months *(200*110%)]

Year 2 payment would be:
Lease A: [4 months * 100*110%] + [8 months *(110*110%)]
Lease B: [9 months * 200*110%] + [3 months *(220*110%)]

The resulting output should look like:
Year1 Year2
Lease A $1,280 $1,408
Lease B $,2460 $2,772

I need to do this for nearly 200 leases.
Sorry for the long post -- any help is greatly appreciated. Thanks in
advance.

Allan

 

[Jack]

Somthing like

select
a.lease_id,
sum(b.lease_payment) as y1_payment,
sum(c.lease_payment) as y2_payment,
from
leases a, payment b, payment c
where
a.lease_id = b.lease_id and
a.lease_id = c.lease_id and
b.payment_date between 9/1/2006 and 8/31/2007 and
c.payment_date between 9/1/2007 and 8/31/2008 and
group by lease_id;


might work

 

[Guest]

Thanks for the reply Jack. My payment table records have a field for the
start date, end date and payment amount (which is the payment per month).
Summing the monthly amount will not get the desired result -- it needs to be
multiplied by the applicable number of months pertaining to the desired year.


I think the query needs to calculate the number of months.....

Thanks. Allan