Effective Date Filtering

[levonbjohnson]

I am trying to filter a database by most recent date in an effective date field, but I want the most recent date BY employee. Here is an example of what the database looks like (with the blue-font rows the ones I want returned):

Employee ID	Name	Job Title	Department	Effective Date	Action	Status
504682	Germain Ifedi	Analyst	Marketing	9/1/2015	Hire	Active
504682	Germain Ifedi	Analyst	Marketing	4/15/2016	Pay Raise	Active
504682	Germain Ifedi	Analyst II	Marketing	5/1/2016	Promotion	Active
701665	Jarran Reed	Specialist	Customer Service	5/20/2016	Hire	Active
985222	CJ Prosise	Analyst	Planning	12/1/2013	Hire	Active
985222	CJ Prosise	Analyst	Planning	4/15/2014	Pay Raise	Active
985222	CJ Prosise	Accountant	Finance	11/3/2014	Transfer	Active
985222	CJ Prosise	Accountant	Finance	4/15/2015	Pay Raise	Active
985222	CJ Prosise	Accountant	Finance	4/30/2016	Retirement	Inactive
128761	Nick Vannett	Specialist	Customer Service	1/13/2016	Hire	Active
128761	Nick Vannett	Specialist	Customer Service	3/27/2016	Termination	Inactive
199744	Rees Odhiambo	Sr Analyst	Planning	8/19/2015	Hire	Active
199744	Rees Odhiambo	Manager	Planning	4/15/2016	Pay Raise	Active

Does anyone have any advice on how to create that filter?

[MarqueeCrew · Accepted solution]

In-DB summarize: group by emp id , max eff date.
Join on these fields to your original data.

If you get duplicate data by emp id + eff date, you will either handle it in db or out.