how to union this query

WITH LatestJob AS (SELECT * FROM(SELECT  
   aId 
   , Position 
   , StartDate 
   ,  Enddate 
   ,SpecializationId
   ,ROW_NUMBER() OVER 开发者_如何学C(PARTITION BY aId ORDER BY  
             CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END ASC,  
             (CAST(Enddate as datetime)) DESC) AS RN 
FROM EmploymentDetails ed) E WHERE RN=1 
)
                      ,EarliestStart AS (SELECT aID
                                                , sum(DATEDIFF(YEAR,Startdate, isnull(Enddate,getdate()))) AS YearsExperience 
                                         FROM EmploymentDetails GROUP BY aId)
                      SELECT u.Id,u.FirstName + ' ' + u.LastName AS NAME
                      , lj.Position AS LatestPosition
                      ,aps.cId,aps.ApStatusID as ApplicationStatus,aps.sId as SpecializationId
                      ,  YearsExperience
                      , ad.ExpectedSalary, REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
                      FROM Users u JOIN LatestJob lj ON u.Id = lj.aid 
                  JOIN EarliestStart ye ON ye.aId = u.Id 
                  JOIN ApplicantDetails ad ON ad.aId = u.Id
                  JOIN ApplicationStatus aps ON aps.aId=u.Id    


WITH LatestJob AS (SELECT * FROM(SELECT  
   aId 
   , Position 
   , StartDate 
   ,  Enddate 
   ,SpecializationId    
   ,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY  
             CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END ASC,  
             (CAST(Enddate as datetime)) DESC) AS RN 
FROM EmploymentDetails ed) E WHERE RN=1 
)
                      ,EarliestStart AS (SELECT aID
                                                , sum(DATEDIFF(YEAR,Startdate, isnull(Enddate,getdate()))) AS YearsExperience 
                                         FROM EmploymentDetails GROUP BY aId)
                      SELECT u.Id,u.FirstName + ' ' + u.LastName AS NAME
                      , lj.Position AS LatestPosition
                      ,  YearsExperience
                      , ad.ExpectedSalary, REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
                      FROM Users u JOIN LatestJob lj ON u.Id = lj.aid 
                  JOIN EarliestStart ye ON ye.aId = u.Id 
                  JOIN ApplicantDetails ad ON ad.aId = u.Id
                    where u.RoleId=3 
                    and u.UserStatusId=1
                    and lj.SpecializationId=38 --@sId

---

I think this might do it (I also made it a little more readable):

    WITH LatestJob AS (
    SELECT * FROM
        (SELECT  
            aId 
           ,Position 
           ,StartDate 
           ,Enddate 
           ,SpecializationId
           ,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY 
                 (CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END) ASC
                ,CAST(Enddate AS datetime) DESC
            ) AS RN 
        FROM 
            EmploymentDetails ed
        ) E 
    WHERE RN = 1 
    )
    ,EarliestStart AS (
        SELECT 
             aID
            ,SUM(DATEDIFF(YEAR, Startdate, ISNULL(Enddate,GETDATE()))) AS YearsExperience 
        FROM 
            EmploymentDetails 
        GROUP BY 
            aId
    )
    (
    SELECT
         u.Id
        ,u.FirstName + ' ' + u.LastName AS NAME
        ,lj.Position AS LatestPosition
        ,aps.cId
        ,aps.ApStatusID AS ApplicationStatus
        ,aps.sId AS SpecializationId
        ,YearsExperience
        ,ad.ExpectedSalary
        ,REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
    FROM 
        Users u 
    JOIN 
        LatestJob lj ON u.Id = lj.aid 
    JOIN 
        EarliestStart ye ON ye.aId = u.Id 
    JOIN 
        ApplicantDetails ad ON ad.aId = u.Id
    JOIN 
        ApplicationStatus aps ON aps.aId=u.Id    
    )
UNION ALL
    (
    SELECT 
         u.Id
        ,u.FirstName + ' ' + u.LastName AS NAME
        ,lj.Position AS LatestPosition
        ,YearsExperience
        ,ad.ExpectedSalary
        ,REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
    FROM 
        Users u 
    JOIN 
        LatestJob lj ON u.Id = lj.aid 
    JOIN 
        EarliestStart ye ON ye.aId = u.Id 
    JOIN 
        ApplicantDetails ad ON ad.aId = u.Id
    WHERE 
        u.RoleId = 3 
        AND u.UserStatusId = 1
        AND lj.SpecializationId = 38 --@sId
)

Hopefully I didn't lose any information or copy it down wrong along the way. It only parses correctly so I'm not sure if it will actually return what you wish.

---

Try the following:

WITH LatestJob AS (
    SELECT * FROM (
        SELECT  
           aId 
           , Position 
           , StartDate 
           ,  Enddate 
           ,SpecializationId
           ,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY  
                     CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END ASC,  
                     (CAST(Enddate as datetime)) DESC) AS RN 
        FROM EmploymentDetails ed) E WHERE RN=1 
    )
), EarliestStart AS (
    SELECT aID, sum(DATEDIFF(YEAR,Startdate, isnull(Enddate,getdate()))) AS YearsExperience 
    FROM EmploymentDetails GROUP BY aId
)

  SELECT u.Id,u.FirstName + ' ' + u.LastName AS NAME
      , lj.Position AS LatestPosition
      ,aps.cId,aps.ApStatusID as ApplicationStatus,aps.sId as SpecializationId
      ,  YearsExperience
      , ad.ExpectedSalary, REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
  FROM Users u 
  JOIN LatestJob lj ON u.Id = lj.aid 
  JOIN EarliestStart ye ON ye.aId = u.Id 
  JOIN ApplicantDetails ad ON ad.aId = u.Id
  JOIN ApplicationStatus aps ON aps.aId=u.Id    

  UNION ALL

  SELECT u.Id,u.FirstName + ' ' + u.LastName AS NAME
      , lj.Position AS LatestPosition
      , '' as cId, '' as ApplicationStatus, '' as SpecializationId
      ,  YearsExperience
      , ad.ExpectedSalary, REPLACE(ISNULL(Address1, '') + ', ' + ISNULL(Address2, '') + ', ' + ISNULL(City, ''), ', ,', ',') AS Address
  FROM Users u 
  JOIN LatestJob lj ON u.Id = lj.aid 
  JOIN EarliestStart ye ON ye.aId = u.Id 
  JOIN ApplicantDetails ad ON ad.aId = u.Id
  WHERE u.RoleId=3 
    AND u.UserStatusId=1
    AND lj.SpecializationId=38 --@sId