Find rows using in between values in MySQL

I found it really hard to come up with a descriptive problem title, so I’ll give a bit of context.

We have a list of boats and a list of docks. Boats can be transferred from one dock to the other on a specific datetime, or it could be at no dock at all. I’m trying to find out which boats are at a specific dock at a given moment in time.

Here’s a simplified SQL to illustrate the data model

CREATE TABLE `stackoverflow` (
  `boat_id` int(11) NOT NULL,
  `dock_id` int(11) DEFAULT NULL,
  `startingAt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `stackoverflow` (`boat_id`, `dock_id`, `startingAt`)
VALUES
    (1, 1, '2020-01-01 00:00:00'),
    (1, 2, '2020-02-01 00:00:00'),
    (1, 3, '2020-03-01 00:00:00'),
    (2, 2, '2020-01-01 00:00:00'),
    (2, NULL, '2020-03-01 00:00:00');

The expected output for dock 1 and date 2020-01-15 would be 1.

The expected output for dock 2 and date 2020-02-15 would be 1, 2.

I’ve tried a lot of things including fancy joins but cannot get it to work. Any help is greatly appreciated!

Answer

You can use a correlated subquery to determine which boat is where at a given point in time. The rest is just filtering:

select s.*
from stackoverflow s
where s.startingAt = (select max(s2.startingAt)
                      from stackoverflow s2
                      where s2.boat_id = s.boat_id and
                            s2.startingAt <= '2020-02-15'  -- the time you care about
                     ) and
      s.dock_id = 2;

You can also use window functions to assign a time period for each boat at each dock:

select s.*
from (select s.*,
             lead(startingAt) over (partition by boat_id order by startingAt) as endingAt
      from stackoverflow s
     ) s
where s.startingAt <= '2020-02-15' and
      (s.endingAt > '2020-02-15' or s.endingAt is null) and
      s.dock_id = 2;

Here is a db<>fiddle.