?
Solved

SQL Max Function

Posted on 2007-08-08
4
Medium Priority
?
565 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:njmatt
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 19656353
select d1.*
from deltek.empl_lab_info d1
INNER JOIN
(SELECT Empl_ID, Max(Effect_Dt) EffDt
from deltek.empl_lab_info
GROUP BY Empl_ID ) A
ON d1.Empl_Id = a.Empl_Id AND d1.Effect_Dt = a.EffDt

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19656365
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 )
0
 
LVL 1

Author Comment

by:njmatt
ID: 19656492
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
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 19656507
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
and t.effect_dt = ( select max(i.effect_dt) from deltek.empl_lab_info i where i.empl_id = a.empl_id )
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question