dcsimg
Results 1 to 6 of 6

Thread: Pulling top 3 within record selection

  1. #1
    Join Date
    Apr 2007
    Location
    Heidelberg, Germany
    Posts
    2

    Pulling top 3 within record selection

    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:
    Code:
    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:
    Code:
    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:
    Code:
    Employee | Appraisal Effective | Rating
    A | 1/10/2006 | 5
    A | 1/10/2005 | 4
    A | 1/10/2004 | 5
    Desired Results:
    Code:
    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
    Last edited by chrisdedobb; 04-26-2007 at 03:10 AM.

  2. #2
    Join Date
    Apr 2007
    Posts
    2
    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")

  3. #3
    Join Date
    Apr 2007
    Posts
    2
    the top 4 in the above query can be changed according to ur requirement

  4. #4
    Join Date
    Apr 2007
    Location
    Heidelberg, Germany
    Posts
    2
    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.

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    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).

  6. #6
    Join Date
    Oct 2002
    Location
    Salt Lake City
    Posts
    4
    >> 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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •