MAX(), but only if all values are not null

Question

In SQL Server 2016, I have a table that looks something like this:

dbo.jobs

id	name
0	First Job
1	Second Job

dbo.tasks

id	start_date	end_date
0	2017-04-01	2017-04-3
1	2017-04-02	2017-04-4
2	2017-04-03	null

dbo.job_tasks

id	job_id	task_id
0	0	0
1	1	1
2	1	2

What I'm attempting to do, is to create a view that contains the jobs, with the lowest start and highest end date of the tasks reflecting the start and end date of the jobs. That's relatively easy, with a query in the view like this:

SELECT
   jobs.id,
   jobs.name,
   MIN(tasks.start_date) as job_start_date,
   MAX(task.end_date) as job_end_date,
FROM
   jobs
   LEFT OUTER JOIN job_tasks on jobs.id = job_tasks.job_id
   LEFT OUTER JOIN tasks on job_tasks.task_id = tasks.task_id
GROUP BY jobs.id, jobs.name, job_tasks.job_id, job_tasks.task_id, tasks.task_id

The result from this would look like:

id	name	job_start_date	job_end_date
0	First Job	2017-04-01	2017-04-3
1	Second Job	2017-04-02	2017-04-4

However, if there's a task that has a null end_date, as in the case of the "Second Job", that task isn't completed - so the job_end_date should actually be null, and the correct output would be like this:

id	name	job_start_date	job_end_date
0	First Job	2017-04-01	2017-04-3
1	Second Job	2017-04-02	null

All the aggregate functions ignore null, except for COUNT(). So, my thought at the moment is to use a table valued function, and pass in the job_id. That would limit the scope of the data being processed, and allow me to check for the null end dates and adjust accordingly with a CASE statement. Something like this:

DECLARE @completed bit
SET @completed = 1

IF EXISTS(
   SELECT * FROM job_tasks 
   INNER JOIN tasks on tasks.task_id = job_tasks.task_id AND tasks.end_date IS NULL
   WHERE job_tasks.task_id = @taskId
)
BEGIN
   SET @completed = 0
END

SELECT 
  jobs.id,
  jobs.name,
  MIN(tasks.start_date) as job_start_date,
  CASE WHEN @completed = 0 THEN NULL
  ELSE (MAX(tasks.end_date)) END AS job_end_date
FROM
   jobs
   ... joins and group by clause
WHERE jobs.id = @jobId

Would there be a better approach to getting this view of the data? Perhaps a way to keep the view, without totally compromising performance?

Any advice would be appreciated.

(view and function boilerplate left out)

user_1144035 · Answer

You can use a case expression:

SELECT jobs.id, jobs.name,
       MIN(tasks.start_date) as job_start_date,
       (CASE WHEN COUNT(task.end_date) = COUNT(*)
             THEN MAX(task.end_date)
        END) as job_end_date

user_6053084 · Answer

You could use COALESCE and a big date and a case to translate it back to null, something like

SELECT id, name, job_start_date,
    (CASE WHEN job_end_date <> {d '2100-01-01'}
         THEN job_end_date
    END) as job_end_date
FROM
(SELECT
   jobs.id,
   jobs.name,
   MIN(tasks.start_date) as job_start_date,
   MAX(COALESCE(task.end_date,{d '2100-01-01'} )) as job_end_date,
FROM
   jobs
   LEFT OUTER JOIN job_tasks on jobs.id = job_tasks.job_id
   LEFT OUTER JOIN tasks on job_tasks.task_id = tasks.task_id
GROUP BY jobs.id, jobs.name, job_tasks.job_id, job_tasks.task_id, tasks.task_id) A

This can also be done without the subselect, but it's harder to read and write...

MAX(), but only if all values are not null

2 Answers