Retrieve records created in a Mysql date range

Question

Dear friends,

I have a mysql table that receives manpower effective data in a client of my company.

Each DB record has a start date (When this service began to be provided in this client) and a end date (When this service ceased to be provided.

I need to get the active assets on the client between a date x and a y.

Until then, my SQL solves.

"SELECT * FROM `EFETIVO_POSTO` WHERE `COD` = '$chave' AND `INICIO` >= '$dataIni' AND `FINAL` <= '$dataFin'"

The problem is that if this consultation is carried out with today’s date, it needs to show the effectiveness that exists in that customer today, regardless of the date when this service began to be provided.

Ideas are welcome not only in Mysql but also in PHP and even in JS (Since this will later generate a JSON to be used by JS)

Answer 1

SELECT * FROM EFETIVO_POSTO WHERE COD = $chave AND INICIO <= CURDATE() AND FINAL IS NULL --retorna os efetivos que AINDA estão sendo executados

I imagine that if it has not yet been completed, the final date should be null. Hence the instruction IS NULL

Matching the code you’ve already posted we have:

SELECT * FROM EFETIVO_POSTO WHERE COD = '$chave' AND ((INICIO >= '$dataIni' AND FINAL <= '$dataFin') OR (INICIO <= CURDATE() AND FINAL IS NULL))