Constructing dates based on day of the month

[#1]

completion_date is a date  in my database that can fall on any day of the month

if the completion_date is between 1 and 15(1st through 15th) then I want to create a trial_due_date1 and show the first day of the next month.  For example if completion_date was 12/1/14( between 1 and 15) then trial_due_date1 = 1/1/15 or the first day of next month, else if completion_date >15 but less than the first day of next month then trial_due_date1=2/1/15 (or count 2 months from the completion_date) if the completion_date is null then the trial_due_date should be null.

trial_due_date2 will be the first day of the month after trial_due_date1 regardless.

So I need help with logic for the trial_due_date1 and how to show trial_due_date3 to show as the first day of the month after trial_due_date1

[Clever_Anjos]

Could you elaborate more your requirements about trial_due_date3 and trial_due_date2?

trial_due_date1 should be calculated like below

load

  completion_date,

  MonthStart(completion_date,if(day(completion_date)>15,2,1)) as trial_due_date1;

load

  date(Today() + RecNo()) as completion_date

autogenerate 100;

[#3]

For example the report would look something like this

completion_date           trial_due_date1                  trial_due_date2             trial_due_date3

12/1/2014                        1/1/2015                         2/1/2015                        3/1/2015

12/30/2014                      2/1/2015                         3/1/2015                        4/1/2015

if the completion_date is before the 15th of the month then the trial_due_date1 is 1 month after,  If it is after the 15th then the trial_due_date1 is 2 months after.  Does that help?     

[Clever_Anjos]

Hi spinners1958,

This should solve your needs

load

  completion_date,

  trial_due_date1,

  addmonths(trial_due_date1,1) as trial_due_date2,

  addmonths(trial_due_date1,2) as trial_due_date3;

load

  completion_date,

  MonthStart(completion_date,if(day(completion_date)>15,2,1)) as trial_due_date1;

load

  date(Today() - RecNo()) as completion_date

autogenerate 100;