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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dinesh - 15 Feb 2008 10:58 GMT

Hi experts,

I am working on SQL Server 2005.
Now i have to write a query which will extract some data from 3 to 4
tables.
My main table is having few columns supose 4 columns.

EmpNo EmpName Date_Of_Joining Date_Of_Appraisal

Now records are entering in this table. But here If any emp gets any
appraisal the a new row is entered and first 3 coluns will be same.

for ex

EmpNo EmpName Date_Of_Joining
Date_Of_Appraisal
1    John        21/01/2000 24/01/2001
1    John        21/01/2000 20/01/2002
1    John        21/01/2000 2/01/2007
1    John        21/01/2000 1/01/2008

Now i have to write a query which will have all these columns
and if there is any employee who is having more than one row in the
table then one extra column will be added in the result and It will
show you the 5 columns
And only last two rows will be taken and result will be like this

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

How i can get this result, Please help me.

Any help wil be appriciated.

Regards
Dinesh

Reply to this Message

Plamen Ratchev - 15 Feb 2008 13:20 GMT

This table should be normalized. Here is one way to do the query you need:

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;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Dinesh - 31 Mar 2008 08:37 GMT

> This table should be normalized. Here is one way to do the query you need:
>
[quoted text clipped - 15 lines]
>
> 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

Reply to this Message

Plamen Ratchev - 31 Mar 2008 15:13 GMT

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

Reply to this Message