Finding most recent effectitive salary date

[srini.dw]

Hi,

I need to findout most recent effective date.
Emp No Date Salary
100007 ^1999-11-01^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57




10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^2000-07-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00

Thanks

[panchusrao2656]

In Sort Stage,hash partition the data based on EmployeeID and sort the data by Date descending, please generate the key change indicator which can be used in the transformer/filter to pick the right salary and date. The other option is to extract the recent by writing the proper query.

[chulett]

And? What exactly have you tried? What are the bold values supposed to represent? They don't seem to be the 'most recent' effective dates in the bunches, hence the question...

[srini.dw]

I need to findout most recent salary effective date.

For based on Emp no,Effective Date,Salary.

Emp no Eff Date Salary
100007 ^ 1999-11-01 ^ 000000000002152.57
100007 ^2000-11-01^ 000000000002152.57
100007 ^2001-01-01^ 000000000002152.57
100007 ^2001-11-01^ 000000000002152.57
100007 ^2002-11-01^ 000000000002152.57
100007 ^2003-04-01^ 000000000002152.57
100007 ^2003-09-01^ 000000000002152.57
100007 ^2003-11-01^ 000000000002152.57

result:1999-11-01

10004 ^1998-04-01^ 000000000012500.00
10004 ^1999-04-01^ 000000000012500.00
10004 ^2000-04-01^ 000000000012500.00
10004 ^ 2000-07-01 ^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2000-09-01^ 000000000013350.00
10004 ^2001-02-06^ 000000000013350.00
10004 ^2001-02-28^ 000000000013350.00
10004 ^2001-04-01^ 000000000013350.00
10004 ^2001-08-31^ 000000000013350.00
10004 ^2002-01-01^ 000000000013350.00

result :2000-07-01

[DSguru2B]

Do a group by on Id and Salary and get the MIN() of date.

[chulett]

Huh... I guess our definitions of the phrase 'most recent' differ.

[DSguru2B]

Huh... I guess our definitions of the phrase 'most recent' differ.

[ray.wurlod]

The example showed least recent salary date. Glad I'm not covered but THAT payroll system! For most recent, of course, use MAX() rather than MIN().

[vmcburney]

Looks like it's the earliest effective date of the most recent salary. This makes it tricky as a persons salary may have gone up and down. Eg.

Code: Select all

10001  1999-01-01  000210000
10001  2001-01-01  000240000
10001  2003-01-01  000210000

In this case no combination of aggregation, sort and grouping will give you the right answer. You will probably get the right salary (21000) but the wrong date (1999-01-01).

I think you should sort on EMP and DATE and send it to a transformer. Use Stage Variables to compare current row to previous row. Output the first row, read the second row, if EMP ID and SALARY is the same ignore the row and move to the next row, otherwise output the row. This will give you a unique start date and salary for every employee.

To find the most recent salaries do a remove duplicates on EMP taking the last record (most recent salary).

[DSguru2B]

In other words, sort by Id and Date, pass it via aggregator, group by Id and Salary, retaining MIN() date, pass it via remove duplicate stage and retain last record (most recent salary).