different format / efficiency for MySQL statement

This is a question of different way to run this statement or a way to make it more efficient really.

This statement is meant to name all the staff who will leave the site the soonest, but they staff also have to be on site today.

SELECT 
    e.EmpName,
    cs.SiteName
FROM
    Employee e
INNER JOIN
    EmployeeAssignment ea
ON
    e.EmpId = ea.EmpId
INNER JOIN
    CustomerSite cs
ON
    ea.CustId = cs.CustId
WHERE 
    e.EmpId IN
    (
    SELECT
        ea.EmpId
    FROM
        EmployeeAssignment ea
    WHERE
         ea.EndDate IN
            (
            SELECT 
                MIN(ea.EndDate)
            FROM 
                 EmployeeAssignment ea
             WHERE
                GETDATE() BETWEEN ea.StartDate AND ea.EndDate
             GROUP BY 
                CustId
             ));

if anyone cna think of a better way to write this please reply thank you :)

Answers


Try this

SELECT  
    e.EmpName, 
    cs.SiteName 
FROM 
    Employee e 
INNER JOIN 

(
 SELECT  custid,empid from EmployeeAssignment ea1 where Enddate=( 
             select MIN(ea.EndDate) 
            FROM  
                 EmployeeAssignment ea 
             WHERE 
                GETDATE() BETWEEN ea.StartDate AND ea.EndDate 
                and ea.custid=ea1.custid
             )
) as ea
ON 
    e.EmpId = ea.EmpId 
INNER JOIN 
    CustomerSite cs 
ON 
    ea.CustId = cs.CustId 

This may, or may not work:

SELECT 
    e.EmpName,
    cs.SiteName
FROM
    Employee e
INNER JOIN
    EmployeeAssignment ea
ON
    e.EmpId = ea.EmpId
INNER JOIN
    CustomerSite cs
ON
    ea.CustId = cs.CustId
GROUP BY   
e.EmpName,
cs.SiteName
HAVING ea.EndDate = 
        (
        SELECT 
            MIN(ea.EndDate)
        FROM 
             EmployeeAssignment ea
         WHERE
            GETDATE() BETWEEN ea.StartDate AND ea.EndDate);

Need Your Help

How to capture submit event using jQuery in an ASP.NET application?

asp.net jquery webforms form-submit

I'm trying to handle the submit event of a form element using jQuery.