Getting a Subquery filed to be part of the main SELECT statement
Posted on 2007-04-03
Hello and how are things? I believe this is not as hard as I am making it to be, but I want to find the best way to do this SELECT statemtent. What I have is this:
Select TOP 10 * from dm2_employee dm2E Where Date_of_Term IS NULL AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi') AND EXISTS (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.Employee_Status from dm2_Emp_Activity dm2EA Group BY dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status)
In the subquery, I need to make that MAX(dm2EA.activity_date) part of the data set in the main query. How do I go about doing that with this SELECT statement?