asked on

Getting a Subquery filed to be part of the main SELECT statement

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?

C

Guy Hengel [angelIII / a3]

I would guess some query like this following:

Select TOP 10 *
from dm2_employee dm2E
JOIN (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) l
on <some condition>
Where Date_of_Term IS NULL
AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

however, as you had no joining condition in the exists subquery, you will have to put that yourself...

Corey_819

ASKER

Thanks for your help and suggestion. One more quick question. I am receving a syntax error near the keyword ON. Thoguhts?

Select dm2E.Employee_number, dm2E.Employment_date, dm2E.Last_Name from dm2_employee dm2E JOIN (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) ON (dm2E.employee_number = dm2EA.employee_number) Where dm2E.Date_of_Term IS NULL AND dm2E.control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Corey_819

ASKER

Thanks angellll for your help, DUH... I should have noticed that. Thanks again for your help> :)