Re: Help Needed For writting a query (SQl Server 2005).

Please post the query that you wrote. If you have it exactly as I posted it
will give you the correct results. See the example below with the sample
data you provided:

CREATE TABLE Appraisals (
EmpNo INT,
EmpName VARCHAR(35),
Date_Of_Joining DATETIME,
Date_Of_Appraisal DATETIME,
PRIMARY KEY (EmpNo, Date_Of_Appraisal));

INSERT INTO Appraisals VALUES(1, 'John', '20000121', '20010124');
INSERT INTO Appraisals VALUES(1, 'John', '20000121', '20020120');
INSERT INTO Appraisals VALUES(1, 'John', '20000121', '20070102');
INSERT INTO Appraisals VALUES(1, 'John', '20000121', '20080101');

SELECT EmpNo, EmpName, Date_Of_Joining,
MAX(CASE WHEN seq = 1 THEN Date_Of_Appraisal END) AS
Last_Date_Of_Appraisal,
MAX(CASE WHEN seq = 2 THEN Date_Of_Appraisal END) AS
Previous_Date_Of_Appraisal
FROM (SELECT EmpNo, EmpName, Date_Of_Joining, Date_Of_Appraisal,
ROW_NUMBER() OVER(PARTITION BY EmpNo
ORDER BY Date_Of_Appraisal DESC)
AS seq
FROM Appraisals) AS A
WHERE seq < 3
GROUP BY EmpNo, EmpName, Date_Of_Joining;

The query returns the correct results:

EmpNo EmpName Date_Of_Joining Last_Date_Of_Appraisal
Previous_Date_Of_Appraisal
---------- --------------- ----------------------- -----------------------
--------------------------
1 John 2000-01-21 00:00:00.000 2008-01-01 00:00:00.000
2007-01-02 00:00:00.000

HTH,

Plamen Ratchev
http://www.SQLStudio.com