sql - Query with datetime in informix - Stack Overflow

admin管理员组

文章数量:1278720

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.

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.

• sql
• informix

Share Improve this question Follow asked Feb 24 at 7:18 Cybor696Cybor696 5111 silver badge22 bronze badges 1

• 1 Did you try something? Please show some code. – Yoji Commented Feb 24 at 8:05

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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;

本文标签： sqlQuery with datetime in informixStack Overflow