SQL request excluding periods of time

Question

I need to get all DISTINCT users excluding those who are not available according to unavailability periods of time.

The user table:

+------+-----------+--------------------------------------+
| id   | firstname | content                              |
+------+-----------+--------------------------------------+
| 13   | John      | ...                                  |
| 44   | Marc      | ...                                  |
| 55   | Elise     | ...                                  |
+------+-----------+--------------------------------------+

The unavailability periods table:

+------+-----------+--------------+--------------+
| id   | user_id   | start        | end          |
+------+-----------+--------------+--------------+
| 1    | 13        | 2019-07-01   | 2019-07-10   |
| 2    | 13        | 2019-07-20   | 2019-07-30   |
| 3    | 13        | 2019-09-01   | 2019-09-30   |
| 4    | 44        | 2019-08-01   | 2019-08-15   |
+------+-----------+--------------+--------------|

For example, we want user who are available from 2019-06-20 to 2019-07-05: Marc and Elise are available.

Do I have to use a LEFT JOIN? This request is not working:

SELECT DISTINCT user.*, unavailability.start, unavailability.end,
FROM user
LEFT JOIN unavailability ON unavailability.user_id = user.id
WHERE  
unavailability.start < "2019-06-20" AND unavailability.end > "2019-06-20"
AND unavailability.start < "2019-07-05" AND unavailability.end > "2019-07-05"

And I need as result:

+------+-----------+--------------------------------------+
| id   | firstname | content                              |
+------+-----------+--------------------------------------+
| 44   | Marc      | ...                                  |
| 55   | Elise     | ...                                  |
+------+-----------+--------------------------------------+

With this request I don't get Elise who has no unavailability periods of time.

What would the desired result actually look like? Edit the question accordingly. — Strawberry, Jun 07 '19 at 09:05
You do realise that the dates need quotes around them? (it's probably just a typo in the question). — KIKO Software, Jun 07 '19 at 09:09
I have edited the question. @KIKOSoftware I had forgotten the quotes in my request — London Smith, Jun 07 '19 at 09:14

score 2 · Answer 1 · answered Jun 07 '19 at 09:15

DROP TABLE IF EXISTS user;

CREATE TABLE user

(id SERIAL PRIMARY KEY
,firstname VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO user VALUES
(13,'John'),
(44,'Marc'),
(55,'Elise');

DROP TABLE IF EXISTS unavailability ;

CREATE TABLE unavailability 
(id SERIAL PRIMARY KEY
,user_id INT NOT NULL
,start DATE NOT NULL
,end DATE NOT NULL
);

INSERT INTO unavailability VALUES
(1,13,'2019-07-01','2019-07-10'),
(2,13,'2019-07-20','2019-07-30'),
(3,13,'2019-09-01','2019-09-30'),
(4,44,'2019-08-01','2019-08-15');

SELECT x.* 
  FROM user x 
  LEFT 
  JOIN unavailability y 
    ON y.user_id = x.id 
   AND y.start <= '2019-07-05' 
   AND y.end >= '2019-06-20' 
 WHERE y.id IS NULL;
+----+-----------+
| id | firstname |
+----+-----------+
| 44 | Marc      |
| 55 | Elise     |
+----+-----------+
2 rows in set (0.01 sec)

Not working actually, only Elise is available for the period of time from `2019-01-01` to `2019-12-31` and I get the 3 users: https://www.db-fiddle.com/f/8t3n2YNKyF3ws8KADMCDQ8/0 — London Smith, Jun 07 '19 at 09:35
Working fine actually. Your query differs from mine in some key respects. — Strawberry, Jun 07 '19 at 10:24

score 2 · Answer 2 · answered Jun 07 '19 at 09:19

2

This approach can be used:

select * from user k
where not exists (
select 1 from user 
join unavailability u on u.user_id = user.id
and ('2019-06-20' between start and end or '2019-07-05' between start and end)
where user.id = k.id)

answered Jun 07 '19 at 09:19

Vivek

783
5
11

score 1 · Accepted Answer · answered Jun 07 '19 at 09:21

You can select the ids of the unavailables and use this result in a subquery :

Schema (MySQL v5.7)

CREATE TABLE user (
  `id` INTEGER,
  `firstname` VARCHAR(5),
  `content` VARCHAR(3)
);

INSERT INTO user
  (`id`, `firstname`, `content`)
VALUES
  (13, 'John', '...'),
  (44, 'Marc', '...'),
  (55, 'Elise', '...');



CREATE TABLE unavailability (
  `id` INTEGER,
  `user_id` INTEGER,
  `start` DATETIME,
  `end` DATETIME
);

INSERT INTO unavailability
  (`id`, `user_id`, `start`, `end`)
VALUES
  (1, 13, '2019-07-01', '2019-07-10'),
  (2, 13, '2019-07-20', '2019-07-30'),
  (3, 13, '2019-09-01', '2019-09-30'),
  (4, 44, '2019-08-01', '2019-08-15');

Query #1

SELECT *
FROM user us
WHERE us.id NOT IN (
  SELECT u.user_id
  FROM unavailability u
  WHERE u.start <= '2019-07-05' AND u.end >= '2019-06-20'
);

| id  | firstname | content |
| --- | --------- | ------- |
| 44  | Marc      | ...     |
| 55  | Elise     | ...     |

View on DB Fiddle

Note

This condition :

unavailability.start < 2019-06-20 AND unavailability.end > 2019-06-20
AND unavailability.start < 2019-07-05 AND unavailability.end > 2019-07-05

Will be evaluated like this :

unavailability.start < 2019-06-20 AND unavailability.end > 2019-07-05

Because, for the parts unavailability.start < 2019-06-20 AND unavailability.start < 2019-07-05, everything below 2019-07-05 but above 2019-06-20 will be excluded (you are using AND). The same for both unavailability.end

Didn't know db-fiddle.com really cool :) Thanks for the note... `unavailability.start < 2019-06-20 AND unavailability.start < 2019-07-05` is a bit silly ! :P — London Smith, Jun 07 '19 at 09:28
But only Elise is available from `01-01-2019` to `12-31-2019` and we get the 3 users: https://www.db-fiddle.com/f/8t3n2YNKyF3ws8KADMCDQ8/0 — London Smith, Jun 07 '19 at 09:38
available from date1 to date2 is written start <= date2 and end >= date1. Check this [updated fiddle](https://www.db-fiddle.com/f/8t3n2YNKyF3ws8KADMCDQ8/1) (You've inverted the dates) — Cid, Jun 07 '19 at 09:44

SQL request excluding periods of time

3 Answers3