select trunc(createddate,'dd') timed,trunc(createddate,'hh24') timem,trunc(createddate,'mi') timest, 'created', GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION, avg((lastupdateddate-createddate)*24*60), count(1) obs
from eoc.ON_MOM_MESSAGES_ARCHIVE
WHERE createddate > CAST (:sql_last_value AS date)
group by trunc(createddate,'dd'),trunc(createddate,'hh24'),trunc(createddate,'mi'), GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION
union all
select trunc(createddate,'dd') timed,trunc(createddate,'hh24') timem,trunc(createddate,'mi') timest, 'created', GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION, avg((lastupdateddate-createddate)*24*60), count(1) obs
from  eoc.ON_MOM_MESSAGES
WHERE createddate > CAST (:sql_last_value AS date)
AND status = 3
group by trunc(createddate,'dd'),trunc(createddate,'hh24'),trunc(createddate,'mi'), GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION
union all
select trunc(lastupdateddate,'dd') timed,trunc(lastupdateddate,'hh24') timem,trunc(lastupdateddate,'mi') timest, 'finished',GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION, avg((lastupdateddate-createddate)*24*60), count(1) obs from eoc.ON_MOM_MESSAGES_ARCHIVE
WHERE createddate > CAST (:sql_last_value AS date)
group by trunc(lastupdateddate,'dd'),trunc(lastupdateddate,'hh24'),trunc(lastupdateddate,'mi'), GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION
union all
select trunc(lastupdateddate,'dd'),trunc(lastupdateddate,'hh24'),trunc(lastupdateddate,'mi') timest, 'finished_error', GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION,avg((lastupdateddate-createddate)*24*60), count(1) obs  from  eoc.ON_MOM_MESSAGES
where status = 3
AND createddate > CAST (:sql_last_value AS date)
group by trunc(lastupdateddate,'dd'),trunc(lastupdateddate,'hh24'),trunc(lastupdateddate,'mi'), GROUPID, HANDLERSCRIPT, ERRORDESCRIPTION