CODEWITHSUNDEEP
mysql
gamest
gamest

Reputation: 13

Select where date not in interval

I have two MySQL tables

Payments:
| employeeID | period_begin  | period_end   |

and

Services:
| serviceID  | date      | employeeID   |

I need to find all serviceIDs performed by a given employee whose date is not between any of the period ranges described in Payments. So, for example if I have the following records on Payments and Services for employee 10000:

Payments:
| employeeID    | period_begin  | period_end    |
...
| 10000     | 2013-05-01    | 2013-05-16    |
| 10000     | 2013-05-17    | 2013-06-02    |
| 10000     | 2013-07-01    | 2013-07-16    |
| 10000     | 2013-07-17    | 2013-08-02    |   
...

Services:
| serviceID         | date          | employeeID    |
...
| 2001      | 2013-01-01    | 10000     |
| 2002      | 2013-05-15    | 10000     |
| 2003      | 2013-06-01    | 10000     |
| 2004      | 2013-07-10    | 10000     |
| 2005      | 2013-08-01    | 10000     |
...

The output should be

2001, 2003, 2005

because the dates for services 2002, 2004 are in one of the intervals in the Payments table.

Any ideas? I'm having trouble checking that a service's date is not accounted for one of the intervals recorded on the Payments table. I'm currently joining Services and Payments on employeeID and stating the date condition there, but I'm not getting the right answer; I should probably be joining on a different condition:

select distinct serviceID from Services as X left join Payments as Y on
(X.employeeID=Y.employeeID AND (X.date < Y.period_begin OR X.date > Y.period_end))
where X.employeeID='10000';

is not working.

Upvotes: 1

Views: 1090

Answers (3)

gamest
gamest

Reputation: 13

As indicated by BigToach and Sylvain Leroux, there was clearly a logic-typo confusing an AND with an OR. However, once fixed, that query still doesn't give the right answer. I managed to get the right solution by first finding all serviceIDs for contained in some interval, and then excluding those from the list of all serviceIDs:

    select distinct serviceID from Services as X left join Payments as Y on
    (X.employeeID=Y.employeeID) where X.employeeID='10000' 
    and X.serviceID not in (
       select serviceID from Services as Z join Payments as W on
       (Z.employeeID=W.employeeID and 
       (Z.date between W.period_begin and W.period_end)) 
       where Z.employeeID='10000'
    );

This query, however, is not very pretty as I'm really doing two queries, but it's basically the same thing than Sylvain Leroux first answer, perhaps a bit more human-readable. Maybe there is yet another way we all haven't seen yet. Sylvain's subquery is indeed very nice.

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52030

Concerning the main problem which is "select the services not in any payment range" you could use a LEFT JOIN to keep only the row that does not have a corresponding payment range:

SELECT Services.* FROM Services
 LEFT JOIN
  (SELECT serviceID,period_begin FROM Services 
     JOIN Payments
     USING (employeeID)
     WHERE employeeID = @EID
     AND the_date BETWEEN Payments.period_begin AND Payments.period_end
  ) AS X
 USING (serviceID)
 WHERE employeeID = @EID
 AND period_begin is NULL;

Or, use a subquery -- somewhat more readable, but usually less efficient:

SELECT Services.* FROM Services
WHERE employeeID = @EID
AND serviceID NOT IN (SELECT serviceID FROM Services JOIN Payments
                      USING (employeeID)
                      WHERE employeeID = @EID
                      AND the_date BETWEEN Payments.period_begin AND Payments.period_end);

See http://sqlfiddle.com/#!2/a3557/11

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52030

... AND X.date < Y.period_begin and X.date > Y.period_end

Is obviously impossible (the date cannot be before the start date and after the end date...)

You probably want to write:

... AND (X.date < Y.period_begin or X.date > Y.period_end)

Please wrap the "OR" expression is parenthesis. I think this is important regarding operator precedence and it improves readability.

EDIT: As suggested by @BigToach in a comment, you could use NOT BETWEEN ... AND ... (if the AND word is not too confusing in that context):

... AND (X.date NOT BETWEEN Y.period_begin AND Y.period_end)

Upvotes: 2

Related Questions