MySQL – How to Retrieve Data of Vehicle Drivers Not Assigned to a Job

MySQLperformancequery-performanceselectsubquery

Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:

  • Jobs that have not been assigned to drivers
  • Jobs that have been assigned to drivers
  • If a job has been assigned to a driver then display drivers that are available for the day
  • If there is no jobs for the day then display a list of all drivers available

The last 2 points are what I am really struggling with.

I have the following tables:

drivers ('driver_id', 'driver_name')

vehicles ('vehicle_id', 'vehicle_make', 'vehicle_model')

jobs ('job_id', 'collection_address', 'collection_datetime', 'delivery_address', 'deliver_datetime', 'driver_id', 'vehicle_id')

I have attached the output screen below:

enter image description here

For example if you look at 23/09/2016 the driver Keith is on a job but Nick is available. However, if I do a sub-query it displays both drivers, which is not the correct result. My SQL statement is below and if anyone can help with this, I would really appreciate it.

    SELECT listofdays.job_date, j.job_id, IF( j.driver_id > 0 AND j.job_id > 0, (SELECT driver_name FROM t_drivers WHERE driver_id = j.driver_id LIMIT 1), '') as job_driver, IF( j.vehicle_id > 0, (SELECT vehicle_reg FROM t_vehicles WHERE vehicle_id = j.vehicle_id LIMIT 1), 'no') as job_vehicle, j.collection_town, j.collection_postcode, j.delivery_town, j.delivery_postcode, j.job_status
 FROM (
  SELECT '2016-09-19' + INTERVAL seq.seq DAY AS job_date
  FROM seq_0_to_999999 AS seq
  WHERE seq.seq <= TIMESTAMPDIFF(DAY , '2016-09-19', '2016-09-25' )
 ) AS listofdays
LEFT JOIN t_jobs j ON listofdays.job_date = DATE(j.collection_datetime)
ORDER BY DATE(listofdays.job_date), j.job_order ASC

I am also happy to take onboard any improvements to the above query in terms of speed and performance. It is going to be used for an internal system.

REVISED QUERY BELOW:

    SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  IF(j.job_id IS NULL, sub.available_drivers, 'no availability') AS available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(d.driver_name) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
      AND d.driver_id = j.driver_id                   
GROUP BY listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC

The output is the following:

enter image description here

If you look at the above picture on the 23rd there is 2 open jobs, which should have a list of the available drivers but it is saying no availability. In our case Nick is not working on that day but it says no availability.

Best Answer

I would start with the list of available drivers. As you appear to want them as a CSV string, it makes sense to use grouping and concatenate the names with GROUP_CONCAT(). Use an outer join of jobs to the cross-product of dates and drivers, then, to get the available drivers for each day, group-concatenate the driver name only if it has no matching job:

SELECT
  listofdays.job_date,
  GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
  (
    SELECT
      '2016-09-19' + INTERVAL seq.seq DAY AS job_date
    FROM
      seq_0_to_999999 AS seq
    WHERE
      seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
  ) AS listofdays
  CROSS JOIN t_drivers AS d
  LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                     AND d.driver_id = j.driver_id
GROUP BY
  listofdays.job_date
;

The next, and last, step would be to use the above as a derived table and outer-join t_jobs to it once more – this time to get the job details (and you would also need to outer-join t_drivers and t_vehicle to get details from those tables as well):

SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  sub.available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                         AND d.driver_id = j.driver_id
    GROUP BY
      listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC
;
Related Question