Intervals between two date columns

Question

The problem is this, I have a table that stores the leave (maternity, health, etc...) of an employee, containing the dates of start and end of leave and other information that is not relevant to the problem, this table is used to assemble a payroll of employees for management, and to generate this payroll is passed the reference month.

When informed the month, for example September, the script will do a search for occurrences in the tables that are between the dates from '2019-09-01' until '2019-09-30'. I am having problems with the query only in the employees' license table, the others are being treated perfectly, this is because the time of leave may be longer than any other occurrence, I will illustrate better below.

I have a license that goes from 08/27 to 26/10 totaling 60 days. When generating the payroll for the month 08, returns to me that the employee worked 26 days that month, and for the month 10 returns to me that he worked 4 days, so far is ok, what I’m not getting is to treat the month of September, I can treat the extremes but I can’t treat what is between these two dates, in September he returns me 30 days, the correct would be 0 days, because the employee did not work in September. The same goes for a license from 09/05 to 05/09 I get the extremes but not what is between, which are the months of June, July and August.

Below the SQL script, the php script is quite extensive, as it was tested directly in the database and is not returning me the expected result, I believe the problem is in the query.

tab_funcionario_licenca:

id | id_funcionario |     data_inicial      |      data_final
1  |        2       | '2019-08-27 00:00:00' | '2019-10-26 23:59:00'

Consultation Script with the month of September

SELECT data_inicial, data_final FROM tab_funcionario_licenca WHERE id_funcionario = 2 AND ((data_inicial >= '2019-09-01' AND data_inicial <= '2019-09-30') OR (data_final >= '2019-09-01' AND data_final <= '2019-09-30'))

Result: None

Query Script with the month of August:

SELECT data_inicial, data_final FROM tab_funcionario_licenca WHERE id_funcionario = 2 AND ((data_inicial >= '2019-08-01' AND data_inicial <= '2019-08-31') OR (data_final >= '2019-08-01' AND data_final <= '2019-08-31'))

Resulting: 2019-08-27 00:00:00 until 2019-10-26 23:59:00.

The same goes for the period 2019-10-01 until 2019-10-31, is picking the extremes of the dates not between them as I said, this happens due to the operator OR.

Now a test I did, modifying the operators AND and OR, returns me the date of the employee, the select that was passed was asking if have any record between 01/09 and 30/09, it returns me the employee date with that in the PHP code I make the difference of days worked using the reference date.. as was passed 01/09 until 30/09, is made the calculation of the difference between these dates, which returns 30 - 30 = 0 days worked, but the script no longer works for the dates that are in the extremes...

SELECT data_inicial, data_final FROM tab_funcionario_licenca WHERE id_funcionario = 2 AND (((data_inicial >= '2019-09-01' AND data_inicial <= '2019-09-30') AND (data_final >= '2019-09-01' AND data_final <= '2019-09-30')) OR (('2019-09-01' BETWEEN data_inicial AND data_final) AND ('2019-09-30' BETWEEN data_inicial AND data_final)))

RESULTS: 2019-08-27 00:00:00 2019-10-26 23:59:00

Below the Table Creation Script with the example Insert:

CREATE TABLE `tab_funcionario_licenca` (

  `id` int(5) UNSIGNED NOT NULL,

  `id_funcionario` int(5) UNSIGNED NOT NULL,

  `data_inicial` datetime NOT NULL,

  `data_final` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tab_funcionario_licenca` (`id`, `id_funcionario`, `data_inicial`, `data_final`) 
VALUES
(1, 2, '2019-08-27 00:00:00', '2019-10-26 23:59:00'),
(2, 5, '2019-05-09 00:00:00', '2019-09-05 23:59:00');

Is a solution possible in a single SQL query? Thanks for your help!

Answer 1

See if it answers:

SELECT 
    (CASE WHEN data_inicial < '2019-09-01' THEN '2019-09-01' ELSE data_inicial END) AS inicio, 
    (CASE WHEN data_final > '2019-09-30' THEN '2019-09-30' ELSE data_final END) fim 
FROM tab_funcionario_licenca 
WHERE id_funcionario = 2 AND ((data_inicial <= '2019-09-30' AND data_final >= '2019-09-01');