New field to show Month date based on Start and End date

[alec1982]

Hi,

I have a table that contain:

ID     Expense          Start Date               End Date          Freq

1          $10               2011-06-01             2012-06-31       Monthly

1          $4                 2011-05-1               2011-05-1          OneTime

2          $20               2012-07-01              2014-07-31       Monthly

Looking at the start date and end date, Is there anyway i can have a column to show the Year and month so i can have a row fr each payement.

Like:

ID     Expense          Payment Date          

1          $10               2011-06-01

1          $10              2011-07-01

1          $10               2011-08-01

...

....

...

..

.

1          $10              2012-06-01

The reason I am asking for that is because I need to show the expenses per year.

Thanks for your help

[swuehl · Accepted solution]

You can use a while load for this:

Set DateFormat = 'YYYY-MM-DD';

TEST:

LOAD * INLINE [

ID,     Expense,          Start Date,               End Date,          Freq

1,          $10,               2011-06-01,             2012-06-30,       Monthly

1,          $4 ,                2011-05-1,               2011-05-1,          OneTime

2,          $20 ,              2012-07-01,              2014-07-31,       Monthly

];

RESULT:

LOAD ID, Expense, AddMonths([Start Date],iterno()-1) as [Payment Date]

resident TEST

while [End Date] >= addmonths([Start Date],iterno()-1);

drop table TEST;

[alec1982]

Hi,

Thank you so much for the relpy! you always help me

I still have a question.

Some of the expenses period start like at 209-09-15 and ends up 2012-10-08.

so the last month is not a full month.

Is there anything i can do to make the calculation for the final month?

Thanks,

Alec,

[alec1982]

Hey Swuehl,

Hope you are doing well.

While testing the above script and when having a start Date 2012-07-19 and End Date 2013-07-18

It sems that the script is not showing the last month.

Is there anyway you can help me on this?

Thanks so much for your help always.

Alec,

[swuehl]

Your two dates span a period of exactely one year. I do get 12 payment dates, last on 2013-06-19, so this looks reasonable to me. What do you expect to get, a 13th payment date on 2013-07-19 (which is after the end date)?

[alec1982]

Hi,

Actually, it was my fault that changed the payment date to be at StartMonth so I got one payment less.

I have fixed it by  adding Statrmonth to the while statement. so it will compare apple to apple.

Again, Thank you o much for your help.

[alec1982]

Hi Swuehl,

I was wondering if it is possible to have the payment date reflected by frequency.

like in the sample above it works perfectly when the payment frequency is Monthly.

Now what if the frequency is annual or semi annual or quarterly.would it be possible to have the payment date increases by three months when the frequency is quarterly and by 6 months when it is semi annual and 12 months when it is annuall.

Thxs,

Alec

[swuehl]

Should be possible. Just add a FreqNum to your table (could also be created using conditionals or a mapping table from your original input data):

Set DateFormat = 'YYYY-MM-DD';

TEST:

LOAD * INLINE [

ID,     Expense,          Start Date,               End Date,          Freq, FreqNum

1,          $10,               2011-06-01,             2012-06-30,       Monthly, 1

1,          $4 ,                2011-05-1,               2011-05-1,          OneTime, 1

2,          $20 ,              2012-07-01,              2014-07-31,       Monthly, 1

3,          $100,   2012-07-19, 2013-07-18, Monthly,1

4,$100,   2012-07-19, 2013-07-18, Quarterly, 3

5,$100,   2012-07-19, 2015-07-18, Yearly, 12

];

Then add the FreqNum as factor to addmonths:

RESULT:

LOAD ID, Expense, AddMonths([Start Date],FreqNum*(iterno()-1)) as [Payment Date]

resident TEST

while [End Date] >= addmonths([Start Date],FreqNum*(iterno()-1));

drop table TEST;

[alec1982]

Hi,

I have a frequency field in the table.

I have created a num value to show as follow:

Load

    ExpenseId,

    LuExpenseFrequency as ExpenseFrequency,

    if  ((LuExpenseFrequency ='Monthly'),1,

    If ((LuExpenseFrequency ='Annually'),12,

    If ((LuExpenseFrequency ='English Quarters'),3,

    If ((LuExpenseFrequency ='New Scottish Quarters'),3,

    If ((LuExpenseFrequency ='One-time'),0,

    If ((LuExpenseFrequency ='Quarterly'),3,

    If ((LuExpenseFrequency ='Scottish Quarters'),3,

    If ((LuExpenseFrequency ='Semi-Annually'),6,

        LuExpenseFrequency))))))))  as ExpenseFrequencyNum

Resident tblExpense;

but that loaded more than 20 million line inside the table so I had to abort it.

Would you please tell me if this is the right nrs for frequency above.

Thxs for you rhelp.

[swuehl]

Your one-time expense payments should not show a zero frequency num (which leads to an endless while loop, your addmonth increment is always zero).

For one-time payments, I would use 1, and make sure that your start equals your end date (or the period is smaller than one month).

If you use my above solution, you'll also notice that e.g. quarterly payments are based on the start date (not on start of quarters).