I have a table containing several records associated to the same entities. Two of the fields are dates - start and end dates of a specific period.

Example:

The dates and names vary, but the only rule is that there are NO OVERLAPS - it's a succession of people in charge of a unique role, so there is only one record which is valid for any date.

I have a query returning me a date (let's call it $ThatDay) and what I am trying to do is to find a way to find which name it was at that specific date. For example, if the date was July 4th, 2019, the result of the query I am after would be "John"

I have run out of ideas on how to structure a query to help me find it. Thank you in advance for any help!

CodePudding user response：

you can use a SELECT with BETWEEN as WHERE clause

The date format of MySQL is yyyy-mm-dd , if you keep that you wil never have problems

CREATE TABLE datetab (
  `ID` INTEGER,
  `Name` VARCHAR(4),
  `Start` DATETIME,
  `End` DATETIME
);

INSERT INTO datetab
  (`ID`, `Name`, `Start`, `End`)
VALUES
  ('3', 'Fred', '2022/01/01', '2100/12/31'),
  ('2', 'John', '2018/01/01', '2021/12/31'),
  ('1', 'Mark', '2014/03/22', '2017/12/31');

SELECT `Name` FROM datetab WHERE '2019-07-04' BETWEEN `Start` AND `End`

| Name |
| :--- |
| John |

db<>fiddle here

If ou have a (Sub)- Query with a date as result,you can join it for example

SELECT `Name` 
FROM datetab CROSS JOIN (SELECT '2019-07-04' as mydate FROM dual) t1
WHERE mydate BETWEEN `Start` AND `End`

| Name |
| :--- |
| John |

db<>fiddle here

Also when the query only return one row and column you can use the subquery like this

SELECT `Name` 
FROM datetab 
WHERE (SELECT '2019-07-04' as mydate FROM dual) BETWEEN `Start` AND `End`

| Name |
| :--- |
| John |

db<>fiddle here

CodePudding user response：

Select where the result of your find-date query is between start and end:

select * from mytable
where (<my find date query>)
   between start and end