You are here:

MS SQL Server/SQL Pulling Term date from history

Advertisement


Question
example
example  
I am trying to pull current status information for all rehired staff, but must include the latest termination date from history. I can get all the termination dates to show, but I only need the latest date. The sql is below. I have also attached the results, I am trying to pull the information highlighted in yellow, with the term date highlighted in purple. Can you please advise the best way to accomplish this.

SELECT
ebpsid,
ebase.EbLastName,
ebfirstname,
EeStatus,
EeCategory,
EELatesthiredate,
Eedatelasthire,
EeDateOriginalHire,
MAX(ISNULL(CONVERT(Varchar (12), eemploy.EeTermDate, 101),'')) as TermDate,
Eevestingdate,
Eeadjustedhiredate,
EEvacationeligibilitydate,
EeDateBeg,
EeDateEnd

From ebase

inner join eemploy as eemploy ON eemploy.EeFlxIDEb = ebase.EbFlxID

WHERE EbClock IN ('216762')

group BY ebpsid,ebase.EbLastName,ebfirstname, EeStatus,EeCategory,
EELatesthiredate,Eedatelasthire,EeDateOriginalHire,eeTermDate,
Eevestingdate,Eeadjustedhiredate,EEvacationeligibilitydate,
EeDateBeg,EeDateEnd

Answer
I may be missing something, but it looks like all you need to do is remove eeTermDate from your GROUP BY clause.

If that doesn't give you what you want, let me know. And at the same time tell me the version of SQL you're using, and what "ebClock" represents.

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.