How to list individual columns where each contains a count of ids where the ids in each column are 'not in' a different table for each column in MySQL

Question

I have the following query I am trying to run to determine how much available supply who have not been booked yet I have in an area on a particular day. I do this by generating a table of all workers by area and days available and using 'where' to ensure that they are 'not in' the confirmed booking table for that day.

I have two issues:

1. This is only able to give me my remaining available supply on 1 day. I would like to be able to show the remaining available supply for a series of days. Eg. remaining available supply for the next three days Day1, Day2 and Day3. This would mean that I have to compare my list of workers to a table of bookings for each day, iteratively. So col1 would count worker ids 'not in' Day1's booking table and the worker works on Day1s, col2 would count worker ids 'not in' Day2's booking table and the worker works on Day2s, ... etc.

2. This is a more minor issue. Given the code below, if the count of the available supply is 0, that area row is not in the resulting table. I have tried to use IFNULL on the count but that didn't work. I am therefore having to create a union between that and a query which lists all areas with 0 values and then choose each area with the max value. Is there a better way to do this?

The [daterange_start] fields are filtered fields substituted in by end user of dashboard.

select 
    area, 
    MAX(man_days_remaining)
from (
select doa.zip_code as area, count(w.user_ptr_id) as man_days_remaining
from domestic_worker w 
left join domestic_worker_worker_operating_areas dwoa 
    on w.user_ptr_id = dwoa.worker_id 
left join geography_operatingarea doa 
    on doa.id = dwoa.operatingarea_id 
left join domestic_worker_days_available d 
    on w.user_ptr_id = d.worker_id 
left join geography_city c 
    on c.id = w.worker_city_id
where w.user_ptr_id not in (
    select w.user_ptr_id 
    from booking_booking b 
    left join domestic_jobcard j 
    on b.id = j.booking_id 
    left join domestic_worker w 
    on w.user_ptr_id = j.worker_id 
    where date(b.appointment_datetime) = date([daterange_start]) 
    and b.booking_status = 'confirmed' 
    and w.user_ptr_id is not null) 
and weekday([daterange_start]) + 1 = d.weekday_id
group by 1
UNION
select doa.zip_code as 'Area', '0', null
from geography_operatingarea doa
where doa.active = 1
) t
group by 1
order by 1 asc

Answer 1

The specification isn't entirely clear. Schema definition, sample data and example of expected output would go a long ways towards clarifying it.

So, we're just guessing.

My guess is that something like this would satisfy the requirement:

SELECT t.zip_code AS area
     , MAX(IF(t.i=0,man_days_remaining,0)) AS d0_mdr  -- day 0 man days remaining
     , MAX(IF(t.i=1,man_days_remaining,0)) AS d1_mdr  -- day 1 man days remaining
     , MAX(IF(t.i=2,man_days_remaining,0)) AS d2_mdr  -- day 2 man days remaining
     , MAX(IF(t.i=3,man_days_remaining,0)) AS d3_mdr  -- day 3 man days remaining
 FROM (
        SELECT doa.zip_code
             , n.i
             , COUNT(DISTINCT w.user_ptr_id) AS man_days_remaining
          FROM ( SELECT DATE([daterange_start]) + INTERVAL 0 DAY AS dt ) i
         CROSS
          JOIN ( SELECT 0 AS i
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 UNION ALL SELECT NULL
               ) n
          JOIN geography_operatingarea doa
            ON doa.active = 1
          LEFT
          JOIN domestic_worker_worker_operating_areas dwoa
            ON dwoa.operatingarea_id = doa.id
          LEFT
          JOIN domestic_worker w
            ON w.user_ptr_id = dwoa.worker_id
          LEFT
          JOIN domestic_worker_days_available d
            ON d.worker_id = w.user_ptr_id
           AND d.weekday_id = WEEKDAY(i.dt + INTERVAL n.i DAY) + 1

            -- anti-join confirmed booking
          LEFT
          JOIN domestic_jobcard dj
            ON dj.worker_id = d.worker_id
          LEFT
          JOIN booking_booking bb
            ON bb.id = dj.booking_id
           AND bb.booking_status = 'confirmed'
           AND DATE(bb.appointment_datetime) = i.dt + INTERVAL n.i DAY
         WHERE bb.id IS NULL

         GROUP
            BY doa.zip_code
             , n.i
        ) t
  GROUP BY t.zip_code

This is using an inline view (n) to return integer values 0 through 3. We're going to be adding that to the [daterange_start] to check four dates, starting with [daterange_start].

Then we can join that set to all 'active' doa.

The outer join to get the set of all domestic_worker that are available on those dates.

And then we can use an anti-join pattern to exclude any domestic_worker on a date that there is a confirmed booking.

And we can then get a count of workers available on each day.

The edge case is a doa where all workers for that area are already booked for all four days, the query wouldn't return that doa. We can do a hack to include extra rows for each zip_code... rows that are guaranteed not to match any worker, or booking, and won't be included in any count. A quick way to do that would be to include a NULL value in our generated set of integers.

Then I would wrap that result as an inline view, and then use conditional aggregation to get the count of man days remaining for each day, on a single row for each zip code.

This approach could be extended to any number of days, by adding additional integers (4,5,...) to the generated set, and adding expressions in the SELECT list of the outer query.

This may not satisfy the specification. My understanding of it is quite limited, but this how I would approach it.

---

As an alternative to the anti-join, we could use a NOT EXISTS predicate with a correlated subquery:

SELECT t.zip_code AS area
     , MAX(IF(t.i=0,man_days_remaining,0)) AS d0_mdr  -- day 0 man days remaining
     , MAX(IF(t.i=1,man_days_remaining,0)) AS d1_mdr  -- day 1 man days remaining
     , MAX(IF(t.i=2,man_days_remaining,0)) AS d2_mdr  -- day 2 man days remaining
     , MAX(IF(t.i=3,man_days_remaining,0)) AS d3_mdr  -- day 3 man days remaining
 FROM (
        SELECT doa.zip_code
             , n.i
             , COUNT(DISTINCT w.user_ptr_id) AS man_days_remaining
          FROM ( SELECT DATE([daterange_start]) + INTERVAL 0 DAY AS dt ) i
         CROSS
          JOIN ( SELECT 0 AS i
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 UNION ALL SELECT NULL
               ) n
          JOIN geography_operatingarea doa
            ON doa.active = 1
          LEFT
          JOIN domestic_worker_worker_operating_areas dwoa
            ON dwoa.operatingarea_id = doa.id
          LEFT
          JOIN domestic_worker w
            ON w.user_ptr_id = dwoa.worker_id
          LEFT
          JOIN domestic_worker_days_available d
            ON d.worker_id = w.user_ptr_id
           AND d.weekday_id = WEEKDAY(i.dt + INTERVAL n.i DAY) + 1

            -- exclude matches using NOT EXISTS with correlated subquery
         WHERE NOT EXISTS 
               ( SELECT 1
                   FROM domestic_jobcard dj
                   JOIN booking_booking bb
                     ON bb.id = dj.booking_id
                    AND bb.booking_status = 'confirmed'
                    AND DATE(bb.appointment_datetime) = i.dt + INTERVAL n.i DAY
                  WHERE dj.worker_id = d.worker_id
               )

         GROUP
            BY doa.zip_code
             , n.i
        ) t
  GROUP BY t.zip_code