This is my first post on this forum. I am trying to write a select query that will compare payroll dates to benefit dates for employees working on government building projects. The database is used to track whether employers are paying their employees the federal prevailing wages for working on government buildings and utilities. Throughout the course of a funded project, an employee must receive a certain amount of money. The problem is, wages and benefits change from time-to-time. The following table is results from the wages and benefits query for an employee who's benefits have changed since the start of the project.
Payroll_No Wage Date Fringe Date Employee Class Required Pay Hours Overtime Pay Fringe Expired Benefits 4 10/14/2012 10/14/2012 Employee1 Truck 3 40.44 8.5 FALSE 40.44 17.13 TRUE 4 10/14/2012 11/18/2012 Employee1 Truck 3 40.44 8.5 FALSE 23.31 0 FALSE 5 10/21/2012 10/14/2012 Employee1 Truck 3 40.44 10 FALSE 40.44 17.13 TRUE 5 10/21/2012 11/18/2012 Employee1 Truck 3 40.44 10 FALSE 23.31 0 FALSE 8 11/11/2012 10/14/2012 Employee1 Truck 3 40.44 10 TRUE 52.1 17.13 TRUE 8 11/11/2012 10/14/2012 Employee1 Truck 3 40.44 15.5 FALSE 40.44 17.13 TRUE 8 11/11/2012 11/18/2012 Employee1 Truck 3 40.44 10 TRUE 34.97 0 FALSE 8 11/11/2012 11/18/2012 Employee1 Truck 3 40.44 15.5 FALSE 23.31 0 FALSE 9 11/18/2012 10/14/2012 Employee1 Truck 3 40.44 18 FALSE 32.13 17.13 TRUE 9 11/18/2012 11/18/2012 Employee1 Truck 3 40.44 18 FALSE 15 0 FALSE
In this situation, the employee's health benefits changed on 11/18/2012 ($0.00) from the original date of 10/14/2012 ($17.13) resulting in 5 duplicate records. I need to write a filter that will remove the five invalid rows in the results set. I want to compare the original benefit date (10/14/2012) to payroll dates that happened before the benefit date changed on 11/18/12 and make sure that all future payroll dates don't compare for the original benefit dates. I wrote a filter that excluded all of the payroll dates that were less than the latest benefit date (11/18/12). I also need the filter to remove the record that shows the first payroll date of 10/14/2012 and the benefit date of 11/18/12 which is invalid. Filtering this last record is where I am stuck. Any help would be appreciated.