Doubt - SQL Server - Using Union

Question

Guys, I got the following Union:

select cgc, item, matricula, cota, dataInicio, dataFim 
from [dbo].[cotas] as t1 
where dataFim is null
union
select cgc, item, matricula, cota, dataInicio, dataFim 
from [dbo].[cotas] as t2 
where dataInicio >='01/05/2019' and dataFim<='31/05/2019'

só que eu preciso fazer um resultado final... em que t1.cgc seja diferente de t2.cgc

It turns out that when the Fim date is null, it means that the quota is active. And when it is filled in, the quota is inactive. An employee may have inactive quotas in the CGC that currently works, but there is no way to have active quotas in a CGC that has already worked and no longer works. Only that the employee (enrollment) may have worked in more than one CGC in the same month. I need to take all quotas that are active. And, just take the inactive ones that are from a cgc that he worked on previously.

Does anyone know how to do this with Union? Or should I do it another way? Thank you.

Answer 1

From what you explained you want to take the data as many Active as inactive, you can give an active CGC subquery and perform a inner join in Inactive, the problem is that it will return all inactive quotas of an active employee. Since you need columns to be displayed without duplicity maybe this option is the best.

select 
    t1.* 
from 
    [dbo].[cotas]
where 
    dataFim is null
UNION
select
    t2.*
from 
    [dbo].[cotas] as t2 
    inner join (
        select cgc, item, matricula, cota, dataInicio, dataFim 
        from [dbo].[cotas]
        where dataFim is null) as t3 on t3.cgc <> t2.cgc 
where t2.dataInicio >='01/05/2019' and t2.dataFim<='31/05/2019'

See if this query solves your problem.