Heikki Heikki - 2 months ago
251 0

I have an issue where I would like to nest the latter query into the first one. I need a single result, where each PersonId has three latest JobTitle's as separate columns.

SQL

HO related stuff

SELECT 
DISTINCT 
'TableName' AS [RelationId],
COALESCE(NULLIF(p.FirstName,' ')+' ','') + COALESCE(NULLIF(p.MiddleName,' ')+' ', '') + COALESCE(NULLIF(p.FamilyName,' '),'') AS FullName,
ac.JobTitle,
ac.Company,
ac.ProgressStatus,
p.Nationality,
p.Age,
p.RecordPicture,
p.PersonId,
COALESCE(ac.FitToProfile,'') AS FitToProfile,
CASE 
WHEN [ProgressStatus] IN ('06. Client Longlist','Client Longlist') THEN 'Longlisted candidates'
END AS CandidateGroups,
CASE 
WHEN [ProgressStatus] IN ('06. Client Longlist','Client Longlist')  THEN '06'
END AS CandidateGroupOrder 
FROM AssignmentsCandidatesView ac 
JOIN PeopleView p ON p.PersonId = ac.PersonId
JOIN AssignmentsView a ON a.AssignmentId = ac.AssignmentId
WHERE a.AssignmentId = @assignmentid AND p.PersonId = ac.PersonId AND [ProgressStatus] IN ('06. Client Longlist','Client Longlist') AND ISNULL(ac.CandidateGroup, '') NOT IN ('Exclude')

;SELECT 
cp.JobTitle,
cp.StartDate,
cp.EndDate,
c.Name AS Company
FROM PositionsView cp
JOIN AssignmentsCandidatesView ac ON ac.AssignmentId = @assignmentid
JOIN CompaniesView c ON cp.CompanyId = c.CompanyId
WHERE cp.PersonId = ac.PersonId AND [ProgressStatus] IN ('06. Client Longlist' ,'Client Longlist')