Select items between two dates that are available I wrote this MySQL query, but I feel it could be better. I had to get all clients between today and 14 days' time, that have at least one day "free". I have a SQL Fiddle, and there you can see how my schema looks, how my query looks and what it returns.

SELECT cl.id as clientId, a.id as attendantId, ca.id as caringId, 
ca.startDate, ca.endDate, a.name as attendantName,
IFNULL(
  SUM(eca.effectiveDays),
  0
) AS `sumDays`
FROM client cl
LEFT JOIN 
(
  SELECT ca.id,
  GREATEST(ca.startDate, CURDATE()) as `effectiveStartDate`,
  LEAST(ca.endDate, DATE_ADD(CURDATE(), INTERVAL 14 DAY)) as `effectiveEndDate`,
    DATEDIFF(
      DATE_ADD(LEAST(ca.endDate, DATE_ADD(CURDATE(), INTERVAL 14 DAY)), INTERVAL 1 DAY),
      GREATEST(ca.startDate, CURDATE())
  ) as `effectiveDays`,
  ca.clientId
  FROM caring ca
  WHERE ca.startDate &lt;= DATE_ADD(CURDATE(), INTERVAL 14 DAY)
  AND ca.endDate &gt;= CURDATE()
) eca
ON eca.clientId = cl.id
LEFT JOIN caring ca ON ca.clientId = cl.id 
LEFT JOIN attendant a ON a.id = ca.attendantId 
GROUP BY cl.id HAVING sumDays &lt; DATEDIFF(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 14 DAY), INTERVAL 1 DAY), CURDATE())


Here you can see an example of my data:


In the marked period, I have to return John Doe and Steve Smith, because  John Doe has 3 days free in that time span, and Steve Smith has all days free.

How could I improve this query, or is there any better way of getting desired data?
I will say that you are probably always going to get into really convoluted queries like these until you begin to treat your caring data at its most granular level (a day in this case), rather than as a range of days, and  introduce the concept of a calendar/date "dimension" table that can be joined against to give all dates in a given time range, as is frequently used in scheduling applications like this.

Take for example a case where tables look like:

CREATE TABLE calendar
    (`date_idx` date NOT NULL, PRIMARY KEY `date_idx`)

/* Add all dates you might be interested in from past dates well into the future */
INSERT INTO calendar (`date_idx`) VALUES
('2017-01-01'), ('2017-01-02'), ...

/* This table now only has a single date field */
CREATE TABLE caring
    (`id` int, `careDate` date, `clientId` int, `attendantId` int);


Using these two tables, you can use the following derived table in any query to join against (or use as subselect) to compare dates in caring against the full list of available dates on calendar.

SELECT
    calendar.date_idx AS dateIdx,
    caring.id AS caringId,
    caring.clientId AS clientId,
    caring.attendantId AS attendantId,
FROM calendar
LEFT JOIN caring
    ON calendar.date_idx = caring.caringDate
WHERE
    caring.caringDate IS NULL
    AND dateIdx BETWEEN ? AND ? /* your dates of interest */
ORDER BY dateIdx ASC


This means it would be REALLY simple to extend this to your use case.

SELECT
    client.id AS clientId,
    COUNT(openDates.dateIdx) AS openDateCount
FROM client
LEFT JOIN (
    /* select shown above */
) as openDates
    ON client.id = dateRange.clientId
GROUP BY clientId
HAVING openDateCount &gt; 1


Note that the JOIN and WHERE conditions on the derived table above can easily be changed to get different comparisons of dates in caring to all available dates in calendar.  The real "magic" is really just in having a dimension table which represents the full compliment of available "slots" within the schedule that you can easily join against. You often see similar date/time based dimension table representing different levels of granularity in scheduling applications such as this as well as in data warehouse/reporting applications.

If you search for "time dimension table script" or similar, you will find handy scripts for generating different aggregations/granularity for time into tables that can be used in your applications.



Now this may lead you to the question of how to handle sequences of dates (e.g. a customer orders a 10 day block of services).  This may be a case where you consciously decide to de-normalize your database.

So perhaps you have something like:

CREATE TABLE careOrders
    (`id` int, `careStartDate` date, `careEndDate` date, `clientId` int, `orderDate` date);


Which would be used with:

CREATE TABLE caring
    (`id` int, `careDate` date, `careOrderId` int, `attendantId` int);


Every time you create an order you enter it in the careOrders table and then build the associated items in caring to fill out time slots, leaving a reference in the caring table to the order that populated the item into the table. This is de-normalization of data as you are storing a calculated result of the order start and end dates in caring table which could be seen as redundant to the data in the careOrder table, but in fact is there to allow you to optimize queries against this data.

You perhaps even leave the clientId in the caring table (another conscious decision to break normalization) in order to optimize queries like the one in question here (i.e. to not have to include the careOrders table in the query).



Note how I used date fields instead of varchar in my examples.  You should be using the appropriate data type here.



I noted in your fiddle that none of your tables had appropriate primary keys or indexes.  I don't know if this was simply for benefit of given example (it shouldn't be on Code Review where we want to see actual production code), but this certainly should not be the case in your production environment.  You need appropriate indexes on your tables to allow the queries to be performant.



Consider using meaningful (full) names in your queries.  I know there are a lot of examples out there of people aliasing tables or derived tables with one or two character aliases, but what do you gain in doing this?  You avoid typing a few characters (that most good IDEs could autocomplete anyway)?  So what?  Bias your code (including SQL code) for readability.

Even as I read your SELECT statement, I find myself jumping around in the rest of the complex query trying to find out what a, ca, eca, etc. mean.

Same thing with the fields in your SELECT. They seem to be arbitrarily broken up across lines. Why? It just looks more cluttered to the eye.  Is there really any problem in having a query that extends more vertically down the page (the way the eye naturally wants to move)?

Even if I were to combine my example above in to one full query like:

SELECT
    client.id AS clientId,
    COUNT(openDates.dateIdx) AS openDateCount
FROM client
LEFT JOIN (
    SELECT
        calendar.date_idx AS dateIdx,
        caring.id AS caringId,
        caring.clientId AS clientId,
        caring.attendantId AS attendantId,
    FROM calendar
    LEFT JOIN caring
        ON calendar.date_idx = caring.caringDate
    WHERE
        caring.caringDate IS NULL
        AND dateIdx BETWEEN ? AND ?
    ORDER BY dateIdx ASC
) as openDates
    ON client.id = dateRange.clientId
GROUP BY clientId
HAVING openDateCount &gt; 1


Is that not much easier to read (even without use of aliases)?



Be careful using camelCasing in MySQL schema object names. MySQL tables could suffer from OS-level differences in case sensitivity for their names, while columns, indexes, column aliases, etc. are not case sensitive at all in MySQL. For this reason, many who develop regularly with MySQL prefer snake_case for all schema objects avoid potential problems altogether.



AS is MySQL reserved word and should be strongly considered for UPPERCASE usage in your queries to help differentiate it from you database or value references.  I think you do a good job of uppercasing key and reserved words in all other cases here. as is treated inconsistently.



Why alias some fields in your select clause and not others?  I would think you would want to be consistent here. My guess is since startDate and endDate are not aliased here, and are really not relevant to the result, perhaps they should not be in select at all?