writing a query in SQL Server - 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

General SQL Server Forums

New to SQL Server Programming

writing a query in SQL Server

Author

Topic

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-09-01 : 11:51:24

hi guys, need your expertise in writing query in SQL Server

This is the scenario:

Employee_Number Job_Code Job Description Date
12345 1000 Agent 01/01/2010
12345 2000 Team Leader 01/01/2011

I want the result to be like this:
Employee_Number Job_Code Job Description Date
12345 2000 Team Leader 01/01/2011

I tried using distinct but I'm still getting the 2 rows because of the Job_Code and Date.

Any suggestions you can send will be greatly appreciated.

Thanks so much!

Lizzie :-)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 12:02:31

[code]
WITH emps (Employee_Number, Job_Code, [Job Description], Date, id)
AS (
SELECT Employee_Number, Job_Code, [Job Description], Date, row_number() over(partition by Employee_Number order by date desc) id
FROM yourTable
)
SELECT * FROM Emps WHERE id = 1[/code]

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-09-01 : 15:04:38

thanks Russell, i will try this :-)

Lizzie :-)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 15:16:46

You're welcome. Let us know how it works out and feel free to post whenever you have more questions.

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-09-01 : 17:47:43

hi russell,

i just tried the query you provided and works just what i want to achieve. you guys are so amazing and i'm very happy that i subscribe in this forum. you guys are soooooooo helpful :-)

thanks so much for your help

Lizzie :-)

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-09-02 : 15:02:17

hi russell, hope you're doing good today
i have a new challenges today which relates to the above scenario but a different one.
here's the case, now that i got the record that i want removing the duplicate rows, a new issue arrises.

Employee_Number Job_Code Job Description Date
12345 2000 Team Leader 01/01/2011

if this employee resigned and then rehired and then resigned again after sometime, her new record on my table would be like this:

Employee_Number Job_Code Job Description Hire_Date Term_Date
12345 2000 Team Leader 01/01/2011 01/02/2011
12345 3000 Manager 01/06/2011 01/08/2011

If i will use the over partition function, data would look this this:
Employee_Number Job_Code Job Description Hire_Date Term_Date Row_Num
12345 2000 Team Leader 01/01/2011 01/02/2011 1
12345 3000 Manager 01/06/2011 01/08/2011 2

Now, since on our first query we are filtering Row_Num =1, this is where the problem arises.
I need to get the latest record of the employee based on their maximum Term_Date,
so what i want to achieve is something like this:

Employee_Number Job_Code Job Description Hire_Date Term_Date Row_Num
12345 3000 Manager 01/06/2011 01/08/2011 2

There are a lot of employees in this case on my table.
Any idea how do i proceed and achieve what i want my result to be like?

Thanks in advance!

Lizzie :-)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-06 : 09:34:16

row_number() over(partition by Employee_Number order by term_date desc) id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources