query query!? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query query!?

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-06 : 09:44:22

Hi,
I have the following table,

ref, employref, salary, grade, salaryGradeDate
1, 1010, 10000, MANAGER, 01/01/2008
2, 1010, 12000, SNR MANAGER, 01/10/2008
3, 1010, 25000, DIRECTOR, 01/05/2009
4, 1020, 15000, ADMIN, 01/01/2007

What I require is I want to show the grade of each employ as @ 31/3/2009

Can someone please help.

thanks
Craig.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-06 : 09:51:45

[code]
SELECT e.*
FROM employee e
INNER JOIN
(
SELECT employref, salaryGradeDate = MAX(salaryGradeDate)
FROM employee
WHERE salaryGradeDate <= '2009-03-31'
GROUP BY employref
) a ON e.employref = a.employref
AND e.salaryGradeDate = a.salaryGradeDate
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-06 : 10:22:59

thanks dude.

that done the trick!!

:)

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-08 : 04:53:24

Can I ask a one more question on this.. how would I join this statement above to an existing query, so only to return e.salarygradedate ? table must be joined with employref field?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 05:00:32

[code]
SELECT . . .
FROM . . .
INNER JOIN
(
SELECT e.*
FROM employee e
INNER JOIN
(
SELECT employref, salaryGradeDate = MAX(salaryGradeDate)
FROM employee
WHERE salaryGradeDate <= '2009-03-31'
GROUP BY employref
) a ON e.employref = a.employref
AND e.salaryGradeDate = a.salaryGradeDate
) n ON n.employref = < >
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-08 : 06:29:59

I dont want to inner join this table to my existing sql statement, If i use a left outer would that mess this up?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 06:34:16

not really . . it depends on what do you need.

You can use LEFT JOIN if you have rows that does not have a record in the employee table.

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-08 : 06:38:19

this is the problem.. I want to include the statement above into my sql statement that pulls data from other tables too.. there are numerous joins in my statement, all are LO joins, so I'm abit worried about using an IJ as it may restrict the now of rows returned.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 06:43:23

then use Left Join.

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-08 : 07:09:09

Thanks again!

Craig.

atulchakarvarti
Starting Member

2 Posts

Posted - 2009-05-09 : 09:40:48

hi all,

i have a prob in sql... i have some data in excel file and i embedded that data into sql2000 to remove duplicacy!! my data contains name, telephoneno. ! now the prob is i want to remove duplicate phn nos. and names. but in many rows phn nos. are same but names are different.and i want to del the row where phn no. is duplicate.so plz help me to remove duplicacy in sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-09 : 09:41:50

atulchakarvarti,
please do not cross post and please start a new thread for you issue

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources