rewrite a SQL query

Posted by Admin on 21-Apr-2008 20:49

Hi, I have this SQL query:

SELECT cveempleado, count(DISTINCT fecha)

FROM Con_Incidencias

WHERE tipoinc = 'F' AND fecha >= 12/01/2007 AND fecha <= 12/15/2007

GROUP BY cveempleado

HAVING COUNT(DISTINCT fecha) >= 3

order BY cveempleado.

where this give me the employee ID for employees that have more than three 'F' between two dates. How can write this query using OpenEdge sentences?. Thanks in advance....

All Replies

Posted by Admin on 22-Apr-2008 04:27

Hi Alex,

Don't know if you have it working yet but here is one way to do it...

DEFINE VARIABLE iCount AS INTEGER NO-UNDO.

FOR EACH Con_Incidencias NO-LOCK

WHERE tipoinc = 'F'

AND fecha >= 12/01/2007

AND fecha

BREAK BY cveempleado:

/* Reset the counter */

IF FIRST-OF(cveempleado) THEN

iCount = 0.

/* Increment the counter */

iCount = iCount + 1.

/* Check the counter and display result */

IF LAST-OF(cveempleado) AND iCount >= 3 THEN

DISPLAY cveempleado iCount.

END.

Posted by Admin on 22-Apr-2008 14:56

Ok, it works. Thanks a lot!!!!!

This thread is closed