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.
Any ideas are appreciated.