I am using MS SQL server, and I'm trying to create a SQL query that adds data to a "Reminder" table if data in the "Occurrences" table meets certain criteria. This might be a little difficult to explain. Here's the problem... I have a table "Occurrences" which i updated every week with the absences of all the employee's in the plant. It records the date and time, Name of the employee, and the reason of absence. I need to create a SQL query that will add employees to a "Reminder" table any time they have 3 or more absences in a 90 day time frame.
I've been able to make a query that will put employees in the "Reminder" table if an employee gets 3 absences in the last 90 days, but the problem with that is that I only want to update this table once a week. If it only compares the last 90 days then it could possible miss an employee that deserves to be in the "Reminder" table.
I've been working on this for a long time. Can anyone tell me a better way to attack this problem?