Here's the query:
SELECT c.Name As CompanyName, j.ID as JobID, j.Title as JobTitle,
ja.ApplicationDate, DATEDIFF(MONTH,ja.ApplicationDate, GETDATE()) AS MonthsAgo,
jsc.Name As Recruiter, js.Name As RecruitingAgency, jsh.Name As LastStatus
FROM Companies c
JOIN Jobs j
ON c.ID = j.CompanyID
JOIN JobApplications ja
ON j.ID = ja.JobID
LEFT JOIN JobContact jsc
ON jsc.ID = j.JobSourceContactID
LEFT JOIN JobContactCompany js
ON jsc.JobSourceCompanyID = js.ID
LEFT JOIN (
SELECT TOP 1 jh.JobID, jh.StatusDate, jt.Name
FROM JobStatusHistory jh
JOIN JobStatusTypes jt
ON jh.JobStatusTypeID = jt.ID
--WHERE jh.JobID = j.ID
ORDER BY jh.StatusDate DESC
) jsh
ON j.ID = jsh.JobID
ORDER BY ja.ApplicationDate
I'm trying to get the most recent job status for a particular job. I can't figure out how to do the where clause (the commented WHERE) in the LEFT JOIN. I've done this in the past, but can't remember how I did this in the past.
I will be grateful for any pointers.
You need to use OUTER APPLY. A CROSS Apply is like an INNER JOIN where the applied table must return results, whereas an OUTER Apply is like a [LEFT] OUTER JOIN where the applied subquery may return no results.