Select all available items in a specific period

Question

So I have 2 tables caring and client, like this

client {
  id,
  name
}

caring {
  id,
  startDate,
  endDate,
  clientId
}

I need to get all clients that have at least one day available between two provided dates, you can see my screenshot as reference.

In screenshot I have two clients, and I need to return both of them. As you can see, the first client have three free days (21.5.-23.5.) between provided period (16.5.-29.5.) and the second client have not any caring periods.

So far i have tried something like this

SELECT * FROM client cl
WHERE cl.id NOT IN (SELECT clientId FROM caring 
WHERE endDate >= CURDATE() AND endDate <= DATE_ADD(CURDATE(), INTERVAL 14 DAY))

This one return only clients that don't have carings at all. That is partially what I need because this query don't cover first client from my screenshot. Then I tried query bellow.

SELECT ca.startDate, ca.endDate, cl.firstName, cl.lastName
FROM caring ca
LEFT JOIN client cl on cl.id = ca.clientId
WHERE ca.startDate NOT IN (
    SELECT endDate
    FROM caring 
) AND ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16'

But im not getting desired results.

Any idea how I can achieve this, thx in advance!

Answer 1

Select carings in period of interest and limit start/end dates to this period, respectively. This limitation will allow for easier counting of "booked" i.e. not-free days later on.

SELECT ca.id,
       -- Limit start/end dates to period of interest, respectively
       GREATEST (ca.startDate, '2017-05-16') AS `effectiveStartDate`,
       LEAST (ca.endDate, '2017-05-29') AS `effectiveEndDate`,
       ca.clientId
  FROM carings ca
 WHERE ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16';

Next, count booked days:

DATEDIFF (DATE_ADD (LEAST (ca.endDate, '2017-05-29'), INTERVAL 1 DAY),
          GREATEST (ca.startDate, '2017-05-16'))
   AS `effectiveDays`

Finally, filter out clients that are booked over the whole period. This is done by comparing

• the sum of booked days per client (GROUP BY) to
• the number of days of the whole period (HAVING sumDays < DATEDIFF(...)).

As you want also clients that are not booked at all over the whole period, I would suggest to start from the clients table and "just" LEFT JOIN the (effective) carings:

  SELECT cl.id, cl.name, IFNULL (SUM (eca.effectiveDays), 0) AS `sumDays`
    FROM clients cl
         LEFT JOIN
         (SELECT ca.id,
                 -- Limit start/end dates to period of interest, respectively
                 GREATEST (ca.startDate, '2017-05-16') AS `effectiveStartDate`,
                 LEAST (ca.endDate, '2017-05-29') AS `effectiveEndDate`,
                 DATEDIFF (
                    DATE_ADD (LEAST (ca.endDate, '2017-05-29'), INTERVAL 1 DAY),
                    GREATEST (ca.startDate, '2017-05-16'))
                    AS `effectiveDays`,
                 ca.clientId
            FROM carings ca
           WHERE ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16')
         eca                                               -- effectiveCarings
            ON eca.clientId = cl.id
GROUP BY cl.id, cl.name
  HAVING sumDays <
            DATEDIFF (DATE_ADD ('2017-05-29', INTERVAL 1 DAY), '2017-05-16')
ORDER BY cl.id;

See also http://sqlfiddle.com/#!9/1038b9/19

Answer 2

Select clients whose endDate happens before the last day of your provided period and there's a gap between endDate and startDate during the specified period.

SELECT * FROM client FULL OUTER JOIN caring ON client.id = caring.clientId WHERE endDate <= '2017-05-28' AND DATEDIFF(day, startDate, endDate) > DATEDIFF(day,  '2017-05-16' , endDate);