Need Help .......

[alvinford]

Hi All,

I am facing the following issue, request your help. I have the data which is shown as below.

OfficeStartDate	OfficeEndDate	ContractExtensionDate
16/01/2010	15/04/2013	15/12/2012

Based on the above data my desired output will be as below

OfficeStartDate	OfficeEndDate
16/1/2010	15/12/2012
16/12/2012	15/4/2013

If  ContractExtensionDate is less than my OfficeEndDate then I have to pick up the ContractExtensionDate  as my OfficeEndDate. And ContractExtensionDate +1 will be my OfficeStartDate (In New Row) . And my office end date will be my end Date as shown above.

Thanks in Advance...

Regards,

Alvin.

[CELAMBARASAN · Accepted solution]

Try with this

Load

     Pick(iterno(), OfficeStartDate, Date(Num(ContractExtensionDate) + 1)) AS OfficeStartDate,

     Pick(iterno(), if(ContractExtensionDate < OfficeEndDate, ContractExtensionDate, OfficeEndDate), OfficeEndDate)

     AS OfficeEndDate

Resident TempTable //Assuming your original data is temp table

While

(ContractExtensionDate < OfficeEndDate or iterno() = 1) and iterno() <= 2;

Hope it helps

[alvinford]

Hi Adhimulam,

Very thanks for the reply its helps me . But I have a small concern I have applied the same logic for other rows also but its giving a slight different answer .Below is the example.

Desired Output

OfficeStartDate	OfficeEndDate
01/01/2010	31/12/2012
01/05/2012	31/12/2012
01/05/2012	31/12/2012
01/01/2013	28/02/2013

Current Output According to your logic . I have underlined the row which there is a difference.

OfficeStartDate	OfficeEndDate
01/01/2010	31/12/2012
01/05/2012	31/12/2012
01/01/2013	31/12/2012
01/01/2013	28/02/2013

Can you please help me to solve this .

Thanks very much for your help.

Regards,

Alvin.

[CELAMBARASAN]

Try with this

Load

     Pick(iterno(), OfficeStartDate, Date(Num(ContractExtensionDate) + 1)) AS OfficeStartDate,

     Pick(iterno(), if(ContractExtensionDate < OfficeEndDate

and ContractExtensionDate > OfficeStartDate

, ContractExtensionDate,OfficeEndDate), OfficeEndDate)

     AS OfficeEndDate

Resident TempTable //Assuming your original data is temp table

While

((ContractExtensionDate < OfficeEndDate and ContractExtensionDate > OfficeStartDate) or iterno() = 1) and iterno() <= 2;