We help IT Professionals succeed at work.

SQL syntax for max(date)

urthrilled asked
Last Modified: 2017-05-15
I am working with a salary history table on a report that shows current, last year and 2 years prior history per person.  The salary history table is entered only when an employee gets a raise (or variance i.e. hourly to salary) by employee, type (hourly vs salary) effective date old amount,new amount.

I created 3 views to get the raise information from this year, last year and 2 years prior.  These joined to the current Payroll employees gets me all I need, with the exception of the salaries from 1 and 2 years prior when no raise was received.

I need the simplest, effective way to get the new amount from the salary history table that is the max effective date prior to the year i am reporting.  I've attached an example of an employee hired in 2001, with no raise for 2014-2016.  I need the salary based on the max(date) prior to 1 year and max(date) prior to 2 years.  It seems like it should be that simple, however trying to add it to each year's raise view is not working for me so far.example of information needed
Any ideas are appreciated.
Watch Question

It would be useful to see the "create view" ddl you currently have.
Sr. System Analyst
Unlock this solution and get a sample of our free trial.
(No credit card required)


Thank you so much - sometimes, when I've tried so many things,  all i need is another way to look at it!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.