on 01-26-2015 8:34 PM
Hi All,
My Input Table is as below.
Emp ID Dept ID Salary Date
1 1 100 01/16/2015
2 1 250 01/10/2015
1 1 400 01/25/2015
2 1 500 01/01/2015
I need to sort the data based on Emp ID, Dept ID so expected o/p should be
Emp ID Dept ID Salary Date
1 1 100 01/16/2015
1 1 400 01/10/2015
2 1 250 01/10/2015
2 1 500 01/01/2015
From the above table, For Each Emp ID, Dept ID, pick up the record with latest date. That means final o/p should be
Emp ID Dept ID Salary Date
1 1 100 1/16/2015
2 1 250 1/10/2015
Could you please explain me how to achieve this.
Regards.
Fully explained in Efficient extraction of most recent data from a history table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Add another column in the sorted dataset and store the value gen_row_num_by_group(Emp_ID, Date).
In the final output filter out the records where the groupby column = 1.
kind regards
Raghu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Input Table -> query transform (select Emp ID ,Dept ID and Max(Date) group by Emp ID ,Dept ID) then join this result back to the input table with Emp ID ,Dept ID and date to get the salary
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.