How do I get the latest job completed date for a premises that also has a blank completion date for-CodePudding

The (stripped-down) job table has fields: job.id, job.premises_id, job.completed_date

I need to know premises where job.completed_date is null (a job has been booked but not yet completed) the output must include max(job.completed_date) for that premises (the date of the most recent job that has been completed at that premises.

I tried:

SELECT job.premises_id
, job.jobid
,(SELECT MAX(job.completed_date) FROM job where job.completed_date IS NOT NULL) as 'last job'

FROM job

WHERE job.completed_date is null

CodePudding user response：

Maybe a CTE with a partitioned aggregate?

WITH CTE AS(
    SELECT j.premises_id,
           j.jobid,
           j.completed_date,
           MAX(j.completed_date) OVER (PARTITION BY j.premises_id) AS MaxCompletedDate
    FROM dbo.job)
SELECT premises_id,
       jobid,
       MaxCompletedDate
FROM CTE
WHERE completed_date IS NULL;

CodePudding user response：

Sample data will help your question but assuming you can have multiple rows for a jobid you need to include a correlation in the subquery; Max already excludes null values.

select j.premises_id, j.jobid, (
    select Max(j2.completed_date) 
      from job j2
      where j2.jobId = j.JobId
  ) as [last job]
from job j
where j.completed_date is null;

You may need to also include premises_id in the correlation. Also note delimiters should be either square brackets or double quotes.