Need help on Join conditions

[shyam_uthaman]

Hi,

I need to join these 2 tables

TABLE A

Employee Id	Last evaluated Date	Rating
100	5/1/2015	5
100	5/1/2015	2
100	9/1/2015	3
100	9/1/2015	1
200	2/1/2015	2
200	3/1/2015	5
200	3/1/2015	3

with TABLE B

Employee Id	Salary
100	20
200	50

as you see, table 1 has time dependent data .. I need to join only 1 row of table A with the corresponding employee ID in table B

I need to sort first table by 'last evaluated date' and find the row(s) with the highest value.

If there are multiple rows, sort the rating and take the row with the max rating.

Then, I should get the final state of table A as

Employee Id	Last evaluated Date	Rating
100	9/1/2015	3
200	3/1/2015	5

which I can easily join with table B.

Can you please let me know how we can achieve this by Graphical views?

Or is SQL the only way?

Please let me know of the approaches you can think of with recommendations on the best possible one.

Thanks,

Shyam

[Former Member · Accepted solution]

Hi Shyam,

If the system is in SP9, then we have rank() function available in calculation view(Graphical). Build the rank function partition by employee_id order by last_evealuated_date desc. Then filter out on rank for value 1.

Once this is done, we can join table B in the same calculation view using projection.

If we had both valid start and valid end then we could have used temporal join in Analytic View.

Regards,

Venkat N.

[shyam_uthaman · Accepted solution]

Hi,

Will check it out and let you know.

thanks for the inputs.

Thanks,

Shyam

[shyam_uthaman · Accepted solution]

Hi,

The rank function worked perfectly and it sorted the rows I want to the top .

Like in my example, the result now becomes this .

Employee Id	Last evaluated Date	Rating
100	9/1/2015	3
100	9/1/2015	1
100	5/1/2015	5
100	5/1/2015	2
200	3/1/2015	5
200	3/1/2015	3
200	2/1/2015	2

But as I said, I need only one row per employee, which is now the top row for each employee thanks to the rank function.

I have marked the rows I need in bold. How do I pass only these rows to the output..?

Let me know.

Thanks again.

Shyam

[Former Member · Accepted solution]

Hi Shyam,

Keep the thresh hold value as 1, then you will get required result.

Regards,

Venkat N

[shyam_uthaman · Accepted solution]

Hi Venkat,

Thanks for the reply. But there were 2 sorts required. Order by date and then order by rating.

As I said, there might be multiple values of rating for the same max(date), I need to take only the max(rating) for that max(date)

Your condition -> "partition by employee_id order by last_evealuated_date desc"  will not conserve my rating field for further sorts.

Any thoughts?

Regards,

Shyam

[shyam_uthaman · Accepted solution]

Hi,

I solved this by the following steps.

1. Use rank function to get the single row with a max(date). (Only employee ID and date)

2. Join this with the original table (left outer join with ranked output on the left and original table on the right).

This gave me all ratings back for each max date for each employee.

3. Used rank again but this time ordered by rating partitioned on employee.Threshold = 1

result seems fine.

Let me know if there are better ways.

Else I will mark this as answered.

Thanks,

Shyam

[Former Member · Accepted solution]

Hi Shyam,

Even I feel this is best approach.

Regards,

Venkat N.