asked on

SQL Max Function

Hi Experts,

select * from deltek.empl_lab_info order by empl_id
SNIPPET:
EMPL_ID EFFECT_DT IGNORE COST_AMT IGNORE
00089      1963-09-09 00:00:00      H      75.0000      6500.00
00089      2005-05-01 00:00:00      S      50.4808      4375.00
00089      2004-05-04 00:00:00      H      60.0000      5200.00
00089      2006-02-01 00:00:00      S      50.4808      4375.00

After doing an inner join to another table with EMPL_ID, I want to result the most recent EFFECT_DT for each EMPL_ID

I want to result:
00089      2006-02-01 00:00:00      S      50.4808      4375.00
ONLY

Thanks!

njmatt

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

select * from deltek.empl_lab_info t
where t.effect_dt = ( select max(i.effect_dt) from deltek.empl_lab_info i where i.empl_id = t.empl_id )

njmatt

ASKER

OK Great That worked - That was a lot more complicated than I thought.

I intended to combine my above answer to the select statement, but apparently I'll need help their too. I need it to only updated cost_rate with the most RECENT effect_dt for each EMPL_ID

USE UNIS
GO
UPDATE t
SET cost_rate = HRLY_AMT
from
UNIS.dbo.person_rate t
INNER JOIN UNIS.dbo.person
ON (t.person_key = dbo.person.person_key)
INNER JOIN DLTKCPIS.DELTEK.EMPL_LAB_INFO a
on emp_id = a.EMPL_ID

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial