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

On Feb 15, 6:20=A0am, "Plamen Ratchev" wrote:
> This table should be normalized. Here is one way to do the query you need:=

>
> SELECT EmpNo, EmpName, Date_Of_Joining,
> =A0 =A0 =A0 =A0 =A0 MAX(CASE WHEN seq =3D 1 THEN Date_Of_Appraisal END) AS=

> Last_Date_Of_Appraisal,
> =A0 =A0 =A0 =A0 =A0 MAX(CASE WHEN seq =3D 2 THEN Date_Of_Appraisal END) AS=

> Previous_Date_Of_Appraisal
> FROM (
> =A0 SELECT EmpNo, EmpName, Date_Of_Joining, Date_Of_Appraisal,
> =A0 =A0 =A0 =A0 =A0 =A0 ROW_NUMBER() OVER(PARTITION BY EmpNo
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0ORDER BY Date_Of_Appraisal DESC)
> AS seq
> =A0 FROM Appraisals) AS A
> WHERE seq < 3
> GROUP BY EmpNo, EmpName, Date_Of_Joining;
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Dear Sir,
Thanks for your help.....

But the way you suggested i wrote the query
And I am getting the result like this

EmpNo EmpName Date_Of_Joining Last_Date_Of_Appraisal
Previous_Date_Of_Appraisal
1 John 21/01/2000
1/01/2008 NULL
1 John 21/01/2000
NULL 2/01/2007


While i want result like
EmpNo EmpName Date_Of_Joining Last_Date_Of_Appraisal
Previous_Date_Of_Appraisal
1 John 21/01/2000
1/01/2008 2/01/2007


Please tell me how to achieve this.

Regards
Dinesh Tiwari