List only contracts due by ID and do not consider if ID has contract in force

Question

Fala galera...

I need to list only the EXPIRED contracts, because when the company renews the contract, the data of the expired (old) contract remain in the BD with status "0". So we have: 1 contract expired (old) and 1 in force (new, which was renewed).

And this query is showing me the company that has expired contract registration, but that already has contract in force (renewed) and this is not right, because if she has already renewed the contract, then there is no expired contract on the current date and then it should not appear in the list.

What I need is a list of companies that do not have contract current and loser.

I tried that query, but it didn’t work:

SELECT *

FROM contrato_empresa ce, empresas e

WHERE 

e.emp_id = ce.cte_id_emp AND
ce.cte_status = 0 AND
ce.cte_dt_ini < CURDATE() AND
ce.cte_dt_fim < CURDATE()

ORDER BY e.emp_cidade ASC, e.emp_fantasia ASC

Someone can help me?

Thank you!

Answer 1

First problem in your query, within the where there is that ce.cte_id = ce.cte_id , What’s the point of comparing something to itself? There is no logic, maybe the problem is a c the more, getting ce.cte_id = e.cte_id, but I still believe that there is no such verification because it does not meet the second normal form.

Second problem,

You do a validation that the contract has a shorter start date than the current date, OK ce.cte_dt_ini < CURDATE() But this contract also needs to have a shorter contract end date than the current date, that is to say it needs to be ce.cte_dt_fim > CURDATE() and not ce.cte_dt_fim < CURDATE() as in your query. Your query should then look like this

SELECT *
FROM contrato_empresa ce, empresas e
WHERE 
e.emp_id = ce.cte_id_emp AND
ce.cte_status = 0 AND
ce.cte_dt_ini < CURDATE() AND
ce.cte_dt_fim > CURDATE() AND
ORDER BY e.emp_cidade ASC, e.emp_fantasia ASC