问题描述:

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

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 [email protected]

网友答案:

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 [email protected]
)

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 [email protected]
相关阅读:
Top