Filter rows based on several conditions

Analytics
Highlighted
Enthusiast

Filter rows based on several conditions

I have an employee table where the product is owned by multiple employees. I need to filter 1 employee per product based on below conditions.

1. Emp who has Max stock %

2. If the stock % is equal then go for  employee who has longest tenure (Emp end dt - emp st dt)

3. if the stock % is equal and employee tenure is also equal take the min of emp No.

 

Emp NoStock %Product NoEmp st dtEmp End dt
10.5111/1/1523/03/2018
20.5111/1/1523/03/2018
30.8210/3/131/1/00
60.1527/5/151/1/00
50.0521/6/183/16/18
90.5323/7/200511/1/19
110.5313/12/2016

21/10/2018

 

Expected Result

Emp NoStock %Product No
10.51
30.82
90.53

Accepted Solutions
Teradata Employee

Re: Filter rows based on several conditions

Hi.

 

More homework?

 

Have a look at QUALIFY ROW_NUMBER() OVER (PARTITION BY ProductNo ORDER BY Stock DESC,  (EmpEndDt - EmpStDt) DESC, EmpNo) = 1

 

Cheers.

 

Carlos.

1 ACCEPTED SOLUTION
1 REPLY 1
Teradata Employee

Re: Filter rows based on several conditions

Hi.

 

More homework?

 

Have a look at QUALIFY ROW_NUMBER() OVER (PARTITION BY ProductNo ORDER BY Stock DESC,  (EmpEndDt - EmpStDt) DESC, EmpNo) = 1

 

Cheers.

 

Carlos.