Dates deployment accurding to payment number

[egoziyan]

G'day

I have a sales table that shows

payment_date, sale_ammount, number_of_payments, first_payment, other_payments

that I want to break it into actual payments date

for example:

payment_date = 28-APR-2013,

sale_ammount = 1000,

number_of_payments = 9,

first_payment = 200,

other_payments = 100

I want to see it like this

28-APR-2013, 200, 1

28-MAY-2013, 100, 2

28-JUN-2013, 100, 3

28-JUL-2013, 100, 4

28-AUG-2013, 100, 5

28-SEP-2013, 100, 6

28-OCT-2013, 100, 7

28-NOV-2013, 100, 8

28-DEC-2013, 100, 9

What is the best way to do it?

Thanks in advance.

Yaniv

[Gysbert_Wassenaar · Accepted solution]

Something like this:

Input:

load date#(paymentdate,'DD-MMM-YYYY') as paymentdate,

     sale_amount, number_of_payments,

     first_payment, other_payments

from ...inputsource...;

Result:

load

     addmonths(paymentdate,iterno()-1) as date,

     first_payment + (iterno()-1)*other_payments as amount,

     iterno() as payment_number

resident Input

while iterno() <= number_of_payments;

[egoziyan]

after applying your suggestion I am getting 1 row with the last payment date, I am trying to get a record with diffrent payment date according to the number of payments.

[egoziyan]

Found my problem

It's working

Thanks