TSQL - 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 2005 Forums

Transact-SQL (2005)

TSQL

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-07-29 : 15:20:48

Guys

I have salary records for all employees, but based on latest moddate field value I need to mark down the current salary of the employee.

EMPID SALARY MODDATE CURRENT
1 100 04/08/2007
1 100 04/08/2008 Y
2 200 04/08/2006
2 200 04/08/2005 Y
2 200 04/08/2004

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-29 : 15:31:32

Do you mean something like this?

;with my_cte as (
select
row_number() over (partition by empid,moddate order by empid,moddate desc) as rownum,
empid,
salary,
moddate from your_table)
select
empid,
salary,
moddate,
case
when rownum = 1 then 'Y'
else ''
end as current
from my_cte

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 15:35:42

Are you saying you want to maintain this table with a [current] flag as modifications to salary are made?

So whenever a row is added, you need to find the previous current row, set the flag to null then add the new row with current set to 'y'?

Have you considered just returning the most recent data by empid instead maintaining a [current] flag?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 00:30:26

quote:
Originally posted by amsqlguy

Guys

I have salary records for all employees, but based on latest moddate field value I need to mark down the current salary of the employee.

EMPID SALARY MODDATE CURRENT
1 100 04/08/2007
1 100 04/08/2008 Y
2 200 04/08/2006
2 200 04/08/2005 Y
2 200 04/08/2004

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

Is this what you want?

SELECT t.*
FROM YourTable t
INNER JOIN (SELECT EMPID,MAX(MODDATE) AS MaxDate
            FROM YourTable 
            GROUP BY EMPID)tmp
ON t.EMPID=tmp.EMPID
AND t.MODDATE=tmp.MaxDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources