3
\$\begingroup\$

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 <= DATE_ADD(CURDATE(), INTERVAL 14 DAY)
  AND ca.endDate >= 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 < DATEDIFF(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 14 DAY), INTERVAL 1 DAY), CURDATE())

Here you can see an example of my data:

graphical output 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?

\$\endgroup\$
  • \$\begingroup\$ Can you add table schema and samplee data to your question? Fiddle doesn't seem to be working and without seeing schema and sample data information it is hard to review this question. \$\endgroup\$ – Mike Brant May 22 '17 at 20:25
  • \$\begingroup\$ Hmm fiddle is working for me, can you try to refresh it few times, it should work. \$\endgroup\$ – Super Mario's Yoshi May 22 '17 at 20:27
  • \$\begingroup\$ The fiddle did end up working just seemed to take a very long time to load. \$\endgroup\$ – Mike Brant May 22 '17 at 21:11
  • \$\begingroup\$ Still better to have it directly in the question, so it's self-contained and at less risk of link-rot. \$\endgroup\$ – Toby Speight May 23 '17 at 14:24
4
\$\begingroup\$

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 > 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 > 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?

\$\endgroup\$
  • \$\begingroup\$ Thank you very much for this detailed explanation, to be honest i will need some time to read it carefully and understand what you wanted to say. Also this fiddle is not 100% replica of my production code, on production i dont use varchar for dates, and i have correct primary keys and relationships, i just quickly created this fiddle to emulate my db and query. I marked your answer as correct one, also i would really appreciate if you could create working sql fiddle, that would be big help for me. Thank you one more time. Respect for this. \$\endgroup\$ – Super Mario's Yoshi May 22 '17 at 21:22
  • \$\begingroup\$ @SuperMario'sYoshi Glad to help. You may consider not accepting the answer so quickly so as to help encourage additional reviews. \$\endgroup\$ – Mike Brant May 22 '17 at 21:43
  • \$\begingroup\$ If anyone gives better answer than yours i will gladly accept it. Related to start and end date i actually need it, because i need to show it on UI, and i dont want to make aditional get by id db call in order to get it, so i just take it from here. Also i wanted to ask you do you have any suggestion on my current query, can i improve it somehow ? \$\endgroup\$ – Super Mario's Yoshi May 22 '17 at 21:46
  • \$\begingroup\$ @SuperMario'sYoshi You could easily add careDate or other field into the result set for display. You don't actually need you start and end dates to get to your UI display do you? Having the individual granular dates will do the same thing. My suggestion is to change your schema. Once you start getting queries as complex as those you have shown for operations you need to perform regularly in your application, that should be a red flag that you have a schema problem. \$\endgroup\$ – Mike Brant May 22 '17 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.