Pulling top 3 within record selection

[chrisdedobb]

I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.

What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.

Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.

Any help would be greatly appreciated!! Thanks again!

Records in tblAPPRAISALS Table:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
A | 1/10/2003 | 5
A | 1/10/2002 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2004 | X
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5

My current SQL:

SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating]
FROM tblAPPRAISALS
WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X"
Order by 1, 2 DESC

Results from this Query:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5

Desired Results:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5

[dsatish]

if appraisal and employee are two tables and empid in appraisal table is foreign key then the below query works

i think this will help u


select * from appraisals a where appraisalid in
(select top 4 appraisalid from appraisal b where a.empid=b.empid and Appr_EffDate >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X")

[dsatish]

the top 4 in the above query can be changed according to ur requirement

[chrisdedobb]

All of the data is in one table. This is what they gave me to work with.

There is only one table in the database. It contains four fields. ID (Unique Number), Employee (TXT Name), Appr_Eff (Date), Rating (TXT).

The goal that I am trying to accomplish is to take the top three appraisals for each employee and figure out what that employee's average rating is. I need to do this for each employee.

This would not be so difficult if everyone only had three appraisals. Most employees have at least 4 if not 5-6 appraisals within the last for years.

I am using the other appraisals for other reports. This particular report is based upon a law that states I have to average the three most recent appraisals within a four year period.

Any help? Ideas?

I can get what I need for one employee at a time, but I need it for all employees in one SQL.

[nosepicker]

Give this a try:

SELECT A.Employee, A.[Appraisal Effective], A.Rating
FROM tblAPPRAISALS AS A
JOIN tblAPPRAISALS AS B
ON A.employee = B.employee
AND CONVERT(datetime, A.[Appraisal Effective]) <= CONVERT(datetime, B.[Appraisal Effective])
AND A.Rating <> 'X'
AND B.Rating <> 'X'
GROUP BY A.Employee, A.[Appraisal Effective], A.Rating
HAVING COUNT(*) <= 3

You can remove the "CONVERT(datetime ..." syntax if your dates are already in datetime format (you posted them in character format).

[jcelko]

>> What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X. <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

It would also be nice if you followed the ISO-11179 rules for data element names. And if you knew the ISO-8601 date formats. And if you knew that rows are not records. The term "rating" is too vague -- credit rating? Sexual performance rating?

Assuming all that, would the DDL have looked like this??

CREATE TABLE Appraisals
(emp_id CHAR(3) NOT NULL
REFERENCES Personnel (emp_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
effective_date DATE DEFAULT CURRENT_DATE NOT NULL,
emp_rating CHAR(1) DEFAULT 'X' NOT NULL -- needs a valid name!
CHECK (emp_rating IN ('A', 'B', 'C', 'X')), -- a guess
PRIMARY KEY (emp_id, effective_date));

You will probably want to put this into a VIEW or at least as CTE

CREATE VIEW FourYearAppraisals (emp_id, effective_date, emp_rating)
AS
SELECT emp_id, effective_date, emp_rating
FROM Appraisals AS A
WHERE effective_date
BETWEEN CURRENT_TIMESTAMP - INTERVAL 4 YEARS
AND CURRENT_TIMESTAMP
AND emp_rating <> 'X';

And finally you write:

SELECT A4.*, ROW_NUMBER() OVER (ORDER BY effective_date) AS rn
FROM FourYearAppraisals AS A4
WHERE rn <= 4;