Hello everyone, I have some duplicate data problems in my query:
I have the following query:
SELECT FechaCorte, KeyAfiliado, MAX(FechaPerdido)FechaPerdido
FROM CorteAfiliado
WHERE FechaPerdido IS NOT NULL
GROUP BY FechaCorte,KeyAfiliado
Which returns the following result:
Now, what I need is to make a SELECT to the same table but without grouping, but that returns the LostDate field of the previous query for all the CutDate less than this.
This would be the result I want to obtain:
This is my query:
SELECT A.FechaCorte,A.KeyAfiliado, Z.FechaPerdido FROM CorteAfiliado A
LEFT JOIN (SELECT FechaCorte, KeyAfiliado, MAX(FechaPerdido)FechaPerdido
FROM CorteAfiliado
WHERE FechaPerdido IS NOT NULL
GROUP BY FechaCorte,KeyAfiliado)Z
ON A.KeyAfiliado = Z.KeyAfiliado AND
A.FechaCorte>=Z.FechaPerdido
WHERE A.FECHACORTE BETWEEN '2017-07-04' AND '2019-10-23'
ORDER BY A.FECHACORTE
But I get the following (DUPLICATE VALUES):
Thank you in advance for your help.
I think that with the following you can solve the problem, or at least give you another possible idea :)
I hope it helps you, greetings!