SQL code generation question, group by and aggregates

I'm working on a web application that lets a user design ad-hoc queries against an employee database. The queries are designed in an AJAX web based interface where the user specifies groups of crtieria that get intersected together, i'm trying to add functionality to also allow the user introduce date relationships between crtieria. For example, here's a sample (problematic) generated code for a query that says "Give me all employees that had at least 3 audits 150+ days after they started on the job"

select * FROM  
    (   
        SELECT employee开发者_Go百科_id 
               , max(employee_start_date) employee_start_date  
        from employees 
        where employee_salary_type in (55, 66, 77) 
    group by employee_id having count(*) >= 1
   ) employee_criteria_1,
  ( 
   SELECT employee_id 
             ,max(audit_date) audit_date  
       from employees 
   where job_audit_id in (5, 6, 7)  
   -- They had at least 3 audits
  group by employee_id having count(*) >= 3     
) employee_criteria_2
WHERE 
     employee_criteria_1.employee_id = employee_criteria_2.employee_id 
  -- The audits must have happened at least 150 days after employee's start date
   and   employee_criteria_2.audit_date > employee_criteria_1.employee_start_date + 150

As you notice, each criteria from the UI gets generated into a SQL SELECT block, the all are intersected together. Here's my problem:

The query above checks whether the employee had at least 3 audits, and the last audit MAX occurs 150 days after start date INSTEAD of the 3 audits occur 150+ after start date.

You might ask, "well, why do you have a max(audit_date) statement then?" The reason is that I need to have an aggregate function in order for the group by to work (the group here is generated out of the "occurs at least 3 times" high-level query criteria).

So, what can I add to this code (without much changes, cause i'd like to keep this code generation mechanism) so that i'm now checking that all those 3 occurrences/audits happen 150+ days after (instead of only the max one)??

Thanks!

It sounds like you need to look into window functions, and possibly the having clause rather than the where clause.