sqlinformix

Query with datetime in informix

I have this table in informix:

CREATE TABLE PARTICIPACION(
   idParticipacion serial not null,
   idPersona  integer,
   fechaAlta datetime year to second NOT NULL,
   fechaModificacion datetime year to second,
   idTipoParticipacion smallint,
   isEstado smallint NOT NULL,
   descripción varchar(150)
);

The same idPersona can appear several times.

I need a query that returns the idPersona that have exactly two records and whose fechaModificacion is separated by one year or less.

Solution

  • With exactly 2 values to match, you can use MIN() and MAX() to compare both:

    SELECT idPersona
FROM PARTICIPATION
GROUP BY idPersona
HAVING COUNT(1) = 2
AND MAX(fechaModificacion) <= MIN(fechaModificacion) + INTERVAL (1) YEAR TO YEAR;