Find the month End Dates between the two dates

[Anonymous]

Hello Experts,

I have a table comprises of three columns ID,start_date,End_Date.

ID      Start_date    end_Date

1        06/22/15         01/01/2016

2        03/03/16       

My requirement is to have data in this format

ID      Start_date    end_Date             Month_End_Date

1        06/22/15         01/10/2015           06/30/2015

1       06/22/15         01/10/2015            07/31/2015

1        06/22/15         01/10/2015         08/30/2015

1        06/22/15         01/10/2015          09/31/2015

2        03/03/16                                     03/31/2016

2         03/03/16                                    04/30/2016

Month End date column comprises of the Month end dates between start date and end date.

My final requirment is to map the months where ID is open

(Eg:ID  as 1 is open in the month of June,july,august,September)

Guys pls help!!!

[Kushal_Chawda · Accepted solution]

may be this

Data:

ID,

Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd

Start_Date,

End_date

FROM Table

while Monthname(Start_Date)+iterno()-1 <= monthname(End_date);

[Anonymous]

Thank you kushal,

What if end_date is null?

[Kushal_Chawda]

I think that record will not be affected and will remain the same.

[Anonymous]

thanks Kushal for your valuable time.Can you pls let me know the expression if my End_Date is null...as I need to calculate the count of months till the time ID is open.

Eg above mentioned ID 2 has start_date as 03/03/16

ID   Start_Date    End_Date      Month _End_Date

2     03/03/16                             03/31/16

2     03/03/16                             04/30/16

2     03/03/16                             05/31/16   (On 1st June,2016 if end_date is null)

[Anonymous]

Thanks Kushal,this is what worked for me.

LOAD ID,

     Start_Date,

     Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd,

//     AddMonths(MonthEnd(Start_Date),1) as add_month,

     End_Date

FROM

...

while Monthname(Start_Date)+iterno()-1 <= if(IsNull(End_Date),monthname(today()),monthname(End_Date));

[Kushal_Chawda]

Instead put this condition in script

Data:

LOAD ID,

     Start_Date,

       if(len(trim(End_Date))=0, today(),End_Date) as End_Date

//     AddMonths(MonthEnd(Start_Date),1) as add_month,

FROM Source;

New:

LOAD *,

         Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd

Resident Data

while Monthname(Start_Date)+iterno()-1 <= monthname(End_date);

Drop table Data;