[SOLUCIONADO] me ayudan con la 2° parte de este query?

ingleonidas · #1 (**permalink**) 07/06/2013, 08:07

Estimados..
resulta que tengo el siguiente query:

Código SQL:

Ver originalSELECT 
    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

el que devuelve:

ACTIVIDAD | HORA | CANTIDAD
Jugar 04-JUN-2013 11:00:00 2
Trabajar 04-JUN-2013 11:00:00 3
Trabajar 04-JUN-2013 12:00:00 4
Descansar 04-JUN-2013 12:00:00 4
Jugar 04-JUN-2013 13:00:00 1
Jugar 04-JUN-2013 13:00:00 3

No puedo encontrar la forma de sacar los promedios por hora, con el siguiente formato:

ACTIVIDAD | PROMEDIO
Jugar 0,4(2/5 = Actividades en ese horario/ Total en ese horario)
Trabajar 0,6 (3/5)
Trabajar 0,5 (4/8)
Descansar 0,5 (4/8)
Jugar 0,25 (1/4)
Jugar 0,75 (1/4)

Gracias de ante mano a cualquier orientacion
Salu2
leo

Libras · #2 (**permalink**) 07/06/2013, 08:43

teniendo tus datos podemos hacer esto:

Código SQL:

Ver originalCREATE TABLE #temp
(
actividad VARCHAR(20),
horario datetime,
total INT
)    
 
 
INSERT INTO #temp VALUES ('Jugar','04-JUN-2013 11:00:00',2)
INSERT INTO #temp VALUES ('Trabajar','04-JUN-2013 11:00:00',3)
INSERT INTO #temp VALUES ('Trabajar','04-JUN-2013 12:00:00',4)
INSERT INTO #temp VALUES ('Descansar','04-JUN-2013 12:00:00',4)
INSERT INTO #temp VALUES ('Jugar','04-JUN-2013 13:00:00',1)
INSERT INTO #temp VALUES ('Jugar','04-JUN-2013 13:00:00',3)
    
    
SELECT #temp.actividad,total,g_total,CAST(total / CAST(g_total AS DECIMAL(10,4)) AS DECIMAL (10,4)) AS porce  FROM #temp    
LEFT JOIN (SELECT SUM(total) g_total,horario FROM #temp GROUP BY horario) t1
ON (t1.horario=#temp.horario)

saludos!

ingleonidas · #3 (**permalink**) 07/06/2013, 09:07

Entiendo que estas proponiendo crear una tabla temporal donde insertar los resultados de la búsqueda anterior ¿no?

De ser así, no voy a poder ir por esta vía porque estoy consultando sobre bases productivas sobre las cuales solo tengo acceso de lectura..

Había intentado hacer queries anidados..

SELECT xx, xx,
FROM
( todo el query anterior)

pero no consigo hacer la operación aritmética

Gracias por la mano..

Libras · #4 (**permalink**) 07/06/2013, 09:53

no, no te propongo una tabla temporal, yo lo estoy haciendo sobre una temporal para que entiendas la logica del query que estoy realizando...

ingleonidas · #5 (**permalink**) 07/06/2013, 12:27

Entiendo..pero el resultado "cantidad" es un calculo..
vere..
si consigo el resultado lo posteo..
alguna otra sugerencia?
gracias

Libras · #6 (**permalink**) 07/06/2013, 12:55

puedes probar si puedes crear tablas temporales o un cte(esos si te dejan) e implementar una logica parecida a la que te propongo...

ingleonidas · #7 (**permalink**) 07/06/2013, 15:16

no conocia los CTE...y me sirvio para seguir un poco mas.. el query quedo:

Código:

WITH 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 actividad, hora , SUM(CANTIDAD) as prueba
from AllActivities
group by actividad, hora

El resultado

Código:

Actividad                  Horario                             Cantidad
Jugar			04-JUN-2013 12:00:00	      2
Trabajar			04-JUN-2013 12:00:00	      1
Descansar		04-JUN-2013 12:00:00	      3

sigo sin poder sumar la cantidades y hacer el promedio... que quede:

Código:

Actividad                  Horario                             Cantidad
Jugar			04-JUN-2013 12:00:00	      0.33
Trabajar			04-JUN-2013 12:00:00	      0.17
Descansar		04-JUN-2013 12:00:00	      0.5

gracias

Libras · #8 (**permalink**) 07/06/2013, 15:27

amigo ya tienes un cte usa este codigo:

solo sustiyuye la tabla #temp por el nombre de tu cte ;)

Código SQL:

Ver originalSELECT #temp.actividad,total,g_total,CAST(total / CAST(g_total AS DECIMAL(10,4)) AS DECIMAL (10,4)) AS porce  FROM #temp    
LEFT JOIN (SELECT SUM(total) g_total,horario FROM #temp GROUP BY horario) t1
ON (t1.horario=#temp.horario)

ingleonidas · #9 (**permalink**) 10/06/2013, 12:08

Amigo, gracias por la rta
Diculpa mi ignorancia.. como ejecuto la busqueda?

porque intento con union all y nada..
gracias!!

Libras · #10 (**permalink**) 10/06/2013, 12:21

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,total,g_total,CAST(total / CAST(g_total AS DECIMAL(10,4)) AS DECIMAL (10,4)) AS porce  FROM AllActivities    
LEFT JOIN (SELECT SUM(total) g_total,horario FROM AllActivities GROUP BY horario) t1
ON (t1.horario=AllActivities.horario)

ingleonidas · #11 (**permalink**) 10/06/2013, 12:53

Libras, eso lo intente...

este es el error que me da..

Código:

WITH 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,total,g_total,
cast(total / cast(g_total AS decimal(10,4)) AS decimal (10,4)) AS porce  
FROM AllActivities
 LEFT JOIN (SELECT SUM(total) g_total,horario FROM AllActivities GROUP BY horario) t1 ON (t1.horario=AllActivities.horario)

SQL0206N  "HORARIO" no es válida en el contexto donde se usa.  SQLSTATE=42703

SQL0206N  "HORARIO                                                               " 
no es válida en el contexto donde se usa.

Libras · #12 (**permalink**) 10/06/2013, 13:20

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,total,g_total,
CAST(total / CAST(g_total AS DECIMAL(10,4)) AS DECIMAL (10,4)) AS porce  
FROM AllActivities
 LEFT JOIN (SELECT SUM(total) g_total,hora FROM AllActivities GROUP BY hora) t1 ON (t1.hora=AllActivities.hora)

ahora prueba con esto :S

ingleonidas · #13 (**permalink**) 10/06/2013, 13:48

Amigo, yo sigo intentando tmb con otras combinaciones y nada..
con tu propuesta sale el siguiente error:

Cita:

QL0206N "TOTAL" no es válida en el contexto donde se usa. SQLSTATE=42703

SQL0206N "TOTAL " no es válida en el contexto donde se usa.

:S

Libras · #14 (**permalink**) 10/06/2013, 14:11

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)

ingleonidas · #15 (**permalink**) 10/06/2013, 14:16

Señor Libras,
le encomiendo su buena voluntad a los dioses para que se propague a toda la comunidad!

Gracias

el resultado:

Cita:

Jugar 1 3 0.3333
Jugar 2 6 0.3333
Trabajar 1 3 0.3333
Trabajar 1 6 0.1666
Trabajar 4 5 0.8000
Trabajar 1 1 1.0000
Descansar 1 3 0.3333
Descansar 3 6 0.5000

salu2
leonidas

Libras · #16 (**permalink**) 10/06/2013, 14:24

de nada un placer :)