SELECT * from (SELECT MSGID,
EXTRACT(HOUR FROM (ACKRCVTIME - ACPTIME)) DELHOUR,
EXTRACT(MINUTE FROM (ACKRCVTIME - ACPTIME)) DELMIN,
EXTRACT(SECOND FROM (ACKRCVTIME - ACPTIME)) DELSEC
FROM SA_OUTGOINGMSGLOG WHERE (ACKRCVTIME IS NOT NULL))a ;
SELECT a.MSGID, ((a.DELHOUR*60*60) + (a.DELMIN*60) +(a.DELSEC)) DELIVERYTIME
FROM (SELECT MSGID,
EXTRACT(HOUR FROM (ACKRCVTIME - ACPTIME)) DELHOUR,
EXTRACT(MINUTE FROM (ACKRCVTIME - ACPTIME)) DELMIN,
EXTRACT(SECOND FROM (ACKRCVTIME - ACPTIME)) DELSEC
FROM SA_OUTGOINGMSGLOG WHERE (ACKRCVTIME IS NOT NULL))a ;
SELECT COUNT(*)
FROM (SELECT a.MSGID, ((a.DELHOUR*60*60) + (a.DELMIN*60) +(a.DELSEC)) DELIVERYTIME
FROM (SELECT MSGID,
EXTRACT(HOUR FROM (ACKRCVTIME - ACPTIME)) DELHOUR,
EXTRACT(MINUTE FROM (ACKRCVTIME - ACPTIME)) DELMIN,
EXTRACT(SECOND FROM (ACKRCVTIME - ACPTIME)) DELSEC
FROM SA_OUTGOINGMSGLOG WHERE (ACKRCVTIME IS NOT NULL))a ) b
WHERE b.DELIVERYTIME > 1;
SELECT COUNT(*) FROM SA_OUTGOINGMSGLOG WHERE STATUS='6';