Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Thank You Very Much