July 29, 2008 at 1:20 pm
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.
EMPIDSALARYMODDATE CURRENT
1100 04/08/2007
110004/08/2008Y
220004/08/2006
220004/08/2005Y
220004/08/2004
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
July 29, 2008 at 1:31 pm
select
EMPID,
SALARY,
MODDATE
from
MyTable a
join
(
select
bb.EMPID,
MODDATE = max(bb.MODDATE)
from
MyTable bb
group by
bb.EMPID
) b
on a.EMPID = b.EMPID and a.MODDATE = b.MODDATE
order by
a.EMPID
July 29, 2008 at 1:33 pm
no chance you can do it like so....
--Setting up test data
CREATE TABLE #tmp (
empID INT,
Salary INT,
ModDate DATETIME,
Currentsalaray BIT)
INSERT INTO #tmp
SELECT 1,100,'4/08/2007',''
UNION
SELECT 1,100,'4/08/2008',1
UNION
SELECT 2,200,'4/08/2006',''
UNION
SELECT 2,200,'4/08/2005',1
UNION
SELECT 2,200,'4/08/2004',''
--Doing the update
UPDATE t1
SET [Salary] = [Salary] - ([Salary] * .15)
FROM [#tmp] t1
INNER JOIN (
SELECT MAX(ModDate) AS MaxDate, [empID]
FROM #tmp
GROUP BY [empID]) t2
ON t1.empID = t2.EmpID
AND t1.ModDate = t2.MaxDate
SELECT *
FROM #tmp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply