Autogenerate?

[siva0606]

Hi all,

I have the table like this,

EmployeeNo	FromDate	ToDate	Salary
123	1/1/2011	1/7/2011	3000
123	1/8/2011	1/7/2012	5000

Now i want to create table like this,

EmployeeNo	Year	Month	Salary
123	2011	Jan	3000
123	2011	Feb	3000
123	2011	Mar	3000
123	2011	Apr	3000
123	2011	May	3000
123	2011	Jun	3000
123	2011	Jul	3000
123	2011	Aug	3000
123	2011	Sep	3000
123	2011	Oct	3000
123	2011	Nov	3000
123	2011	Dec	3000
123	2012	Jan	5000
123	2012	Feb	5000
123	2012	Mar	5000
123	2012	Apr	5000
123	2012	May	5000
123	2012	Jun	5000

I have hundreds of employee numbers. Kindly all help me to solve this problem.

Thanks and Regards,

Sivasu

[jagan]

Hi Priya,

Please find my explanation below, hope I clearly explained.

//Function to calculate number of Months Difference between first and second data ($1) refers first and ($2) refers second date parameter

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

Directory;

LOAD

    *,

    Month(SplitDate) AS Month,

    Year(SplitDate) AS Year;

//Using While here iterating every record to create (ToDate - FromDate) number of months difference,  IterNo() keeps track of Iteration number of each record

LOAD

    *,

    AddMonths(FromDate, IterNo() -1) AS SplitDate  //New date field to get incremental date for every month

WHILE IterNo() < NumSum($(MonthDiff(FromDate, ToDate)),  1);   

//Converting date format in this step

LOAD

    EmployeeNo,

    Date(Date#(FromDate, 'D/M/YYYY')) AS FromDate,

    Date(Date#(ToDate, 'D/M/YYYY')) AS ToDate,

    Salary;

LOAD * INLINE [

EmployeeNo,FromDate,ToDate,Salary

123,1/1/2011,1/7/2011,3000

123,1/8/2011,1/7/2012,5000

];

Regards,

Jagan.

[#3]

Thank You Very Much