We help IT Professionals succeed at work.

SQL Query, return newest entry per Empl ID in query that returns several results per EMPL ID

Medium Priority
331 Views
Last Modified: 2012-05-12
I have the following query:

SELECT     DELTEK.EMPL_LAB_INFO.EMPL_ID, DELTEK.EMPL.LAST_FIRST_NAME, DELTEK.EMPL_LAB_INFO.EMPL_CLASS_CD, DELTEK.EMPL_LAB_INFO.ORG_ID,
                      DELTEK.EMPL_LAB_INFO.EFFECT_DT, DELTEK.EMPL_LAB_INFO.S_HRLY_SAL_CD
FROM         DELTEK.EMPL_LAB_INFO INNER JOIN
                      DELTEK.EMPL ON DELTEK.EMPL_LAB_INFO.EMPL_ID = DELTEK.EMPL.EMPL_ID
WHERE     (DELTEK.EMPL.S_EMPL_STATUS_CD = 'ACT') AND (DELTEK.EMPL.TERM_DT IS NULL)

Returns:

EMPL_ID      LAST_FIRST_NAME      EMPL_CLASS_CD      ORG_ID      EFFECT_DT      S_HRLY_SAL_CD
1001      MARK      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1001      MARK      CORE-SLRY      1.1.01      1/25/2004 0:00      S
1001      MARK      PARTNER      1.1.01      1/1/2000 0:00      S
1002      JIM      CORE-SLRY      1.1.01      2/21/2005 0:00      S
1002      JIM      PARTNER      1.1.01      1/1/2000 0:00      S
1002      JIM      CORE-SLRY      1.1.01      1/25/2004 0:00      S
1002      JIM      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      1/25/2004 0:00      S

I need it to only return on record per EID, and that should be the record with the newest effective date.

i.e.

EMPL_ID      LAST_FIRST_NAME      EMPL_CLASS_CD      ORG_ID      EFFECT_DT      S_HRLY_SAL_CD
1001      MARK      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1002      JIM      CORE-SLRY      1.1.01      2/21/2005 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      11/16/2009 0:00      S

Thanks

Comment
Watch Question

Try this

SELECT     DELTEK.EMPL_LAB_INFO.EMPL_ID, DELTEK.EMPL.LAST_FIRST_NAME, DELTEK.EMPL_LAB_INFO.EMPL_CLASS_CD, DELTEK.EMPL_LAB_INFO.ORG_ID,
                     MAX( DELTEK.EMPL_LAB_INFO.EFFECT_DT), DELTEK.EMPL_LAB_INFO.S_HRLY_SAL_CD
FROM         DELTEK.EMPL_LAB_INFO INNER JOIN
                      DELTEK.EMPL ON DELTEK.EMPL_LAB_INFO.EMPL_ID = DELTEK.EMPL.EMPL_ID
WHERE     (DELTEK.EMPL.S_EMPL_STATUS_CD = 'ACT') AND (DELTEK.EMPL.TERM_DT IS NULL)
group by DELTEK.EMPL_LAB_INFO.EMPL_ID
CERTIFIED EXPERT
Commented:
This should do it.

WITH cte1 AS (
SELECT     DELTEK.EMPL_LAB_INFO.EMPL_ID, DELTEK.EMPL.LAST_FIRST_NAME, DELTEK.EMPL_LAB_INFO.EMPL_CLASS_CD, DELTEK.EMPL_LAB_INFO.ORG_ID,
                      DELTEK.EMPL_LAB_INFO.EFFECT_DT, DELTEK.EMPL_LAB_INFO.S_HRLY_SAL_CD,
ROW_NUMBER() OVER (PARTITION BY DELTEK.EMPL_LAB_INFO.EMPL_ID ORDER BY DELTEK.EMPL_LAB_INFO.EFFECT_DT) AS Row
FROM         DELTEK.EMPL_LAB_INFO INNER JOIN
                      DELTEK.EMPL ON DELTEK.EMPL_LAB_INFO.EMPL_ID = DELTEK.EMPL.EMPL_ID
WHERE     (DELTEK.EMPL.S_EMPL_STATUS_CD = 'ACT') AND (DELTEK.EMPL.TERM_DT IS NULL)
)
SELECT EMPL_ID,      LAST_FIRST_NAME,      EMPL_CLASS_CD,      ORG_ID,      EFFECT_DT,      S_HRLY_SAL_CD
FROM cte1
WHERE Row = 1
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please check this article out to see your options:
http://www.experts-exchange.com/A_3203.html

Author

Commented:
lluden, your query is very close, except it is returning the oldest entry for each instead of the newest.

Author

Commented:
lluden, added: DESC to the order by in
:
ROW_NUMBER() OVER (PARTITION BY DELTEK.EMPL_LAB_INFO.EMPL_ID ORDER BY DELTEK.EMPL_LAB_INFO.EFFECT_DT DESC) AS Row

and it seems to be working, let me just double check it real quick.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.