Is a date within some of periods [on hold]

99
November 21, 2018, at 11:10 PM

I have the following table of projects with their activity periods (periods are defined with FROM and TO dates):

ID | ProjID | ActiveFrom | ActiveTo
===+========+============+============
 1 |     20 | 2018-01-01 | 2018-01-20
 2 |     20 | 2018-02-05 | 2018-02-12
 3 |     20 | 2018-02-20 | 2018-02-27
 4 |     30 | 2018-01-15 | 2018-02-15

Of course, a project can have an arbitrary number of activity periods.

I need a SQL query (function) which will return true/false if a given project was active on some given date (is given date within some of project's activity periods).

Answer 1

This function should do what you want. It relies on MySQL treating boolean results as either 1 or 0 in a numeric context, thus the MAX call effectively becomes an OR of all the conditions.

CREATE FUNCTION check_activity(project_id INT, check_date DATE)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  RETURN (SELECT MAX(check_date BETWEEN ActiveFrom AND ActiveTo) FROM projects WHERE ProjId = project_id);
END
SELECT check_activity(20, '2018-01-10'), check_activity(20, '2018-02-01')

Output

check_activity(20, '2018-01-10')    check_activity(20, '2018-02-01')
1                                   0

Demo on dbfiddle

READ ALSO
Is possible to do a ROLLBACK in a MySQL trigger?

Is possible to do a ROLLBACK in a MySQL trigger?

Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?

105
SQL - list of merged contend of two (joined) tables [duplicate]

SQL - list of merged contend of two (joined) tables [duplicate]

This question already has an answer here:

102
Very slow MySQL query performance

Very slow MySQL query performance

I've a query that takes about 18 seconds to finish:

133