Posted by J on 11/07/06 22:12

I am editing a pre-existing view.

This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.

I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.

So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is > GETDATE()?) and the total of deposits up
to that date.

Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.

Example:

for group X of clients...
Deposit 1 due on oct 1: $20
Deposit 2 due on oct 15: $30
Deposit 3 due on nov 15: $40
Deposit 4 due on nov 30: $50

for group Y of clients...
Deposit 1 due on Oct 30: $200
Deposit 2 due on Nov 30: $300
Deposit 3 due on Dec 30: $400

So when if I execute the view today (Nov 7th) each client from group X
should have:
Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)

Group Y should have:
Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)

And so on.

[Back to original message]