7 Replies Latest reply: Oct 8, 2016 3:45 AM by Sunny Talwar

# how to split one record into multipal rows based on date?

hi all

i have a table which has a unique id (ID) ,amount ,StartDate,EndDate.

I have to check the StartDate and EndDate

i.e  if a row contains ID=001 ,  StartDate = 01-jan-2016 and EndDate 01-08-2016

IDStartDateEndDateAmount

00101-Jan-201601-Mar-20161000

I want to split that row by month

i.e

IDStartDateEndDateAmount
00101-Jan-201601-Feb-20161000
00101-Feb-201601-Mar-20161000

How i can split the the rows while loadling ? or is there any other way to do ?

can anyone help me

• ###### Re: how to split one record into multipal rows based on date?

May be like this:

Table:

Date(AddMonths(StartDate, IterNo() - 1)) as StartDate,

Amount

While AddMonths(StartDate, IterNo() - 1) < EndDate;

ID, StartDate, EndDate, Amount

001, 01-Jan-2016, 01-Mar-2016, 1000

];

• ###### Re: how to split one record into multipal rows based on date?

hi sunny

it is not working, it is saying  0 records fetch

• ###### Re: how to split one record into multipal rows based on date?

Why don’t my dates work?

Get the Dates Right

• ###### Re: how to split one record into multipal rows based on date?

it's working

but if StratDate =05-Jan-2016 EndDate= 01-Mar-2016

it should split like

ID    StartDate     EndDate          Amount

001  05-Jan-2016  01-Feb-2016    1000

001 01-Feb-2016   01-Mar-2016    1000

• ###### Re: how to split one record into multipal rows based on date?

Here you go

Table:

Date(If(IterNo() = 1, StartDate, MonthStart(StartDate, IterNo() - 1))) as StartDate,

Date(MonthStart(StartDate, IterNo())) as EndDate,

Amount

While AddMonths(StartDate, IterNo() - 1) < EndDate;