madre mia
Código SQL:
Ver originalWITH AllActivities AS ( SELECT ACTIVITY_NAME AS ACTIVIDAD, TO_CHAR(TRUNC(t.CREATION_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS') AS HORA, COUNT (*) AS CANTIDAD FROM DB2USER.TASKS t
WHERE t.CREATION_TIME> TO_DATE ('04-JUN-2013', 'dd - mon - yyyy')
AND t.CREATION_TIME< TO_DATE ('05-JUN-2013', 'dd - mon - yyyy')
AND t.BPD_NAME = 'CILI - Control Instrumental'
AND t.ACTIVITY_NAME IS NOT NULL
GROUP BY ACTIVITY_NAME, TO_CHAR (TRUNC (t.CREATION_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS') ORDER BY ACTIVITY_NAME,
TO_CHAR (TRUNC (t.CREATION_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS') ASC )
SELECT AllActivities.actividad,cantidad,g_total,
CAST(cantidad / CAST(g_total AS DECIMAL(10,4)) AS DECIMAL (10,4)) AS porce
FROM AllActivities
LEFT JOIN (SELECT SUM(cantidad) g_total,hora FROM AllActivities GROUP BY hora) t1 ON (t1.hora=AllActivities.hora)