Olá Pessoal hoje me deparei com o seguinte problema:
gostaria de somar de cada nome o tempo total gasto entre o intervalo de atividades START e END,
seria simples se pudesse subtrair de cada registro o fim do inicio, mais por exemplo a Mary, iniciou as 13 e foi até 15 e simltaneamnete iniciou outra atividade as 14 e foi até 16, eu gostaria que o resultado dela fosse 3(ela usou 3 horas do seu tempo para realizar as duas atividades) e não 4, pois eu gostaria de somar as horas gastas, não daria para substrair 2014-01-01 16:00:00 de 2014-01-01 13:00:00, por que as atividades podem pular intervalos assim como do Tom.
exemplo:
Name | START | END |
----------------------------------------------------------
KATE | 2014-01-01 13:00:00 | 2014-01-01 14:00:00 |
MARY | 2014-01-01 13:00:00 | 2014-01-01 15:00:00 |
TOM | 2014-01-01 13:00:00 | 2014-01-01 16:00:00 |
KATE | 2014-01-01 12:00:00 | 2014-01-02 04:00:00 |
MARY | 2014-01-01 14:00:00 | 2014-01-01 16:00:00 |
TOM | 2014-01-01 12:00:00 | 2014-01-01 18:00:00 |
TOM | 2014-01-01 22:00:00 | 2014-01-02 02:00:00 |
resultado esperado:
KATE 15 horas
MARY 3 horas
TOM 9 horas
obrigado.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
--UMA BASE FAJUTA
select DTDE,DTATE
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
/*
DTDE DTATE
------------------------- -------------------------
13/06/2013 01:01:01 14/06/2013 01:02:01
13/06/2013 02:01:01 13/06/2013 02:02:01
13/06/2013 13:34:34 13/06/2013 15:02:01
13/06/2013 09:34:34 13/06/2013 09:56:56
*/
--DIFERENCA ENTRE DATAS
select (DTATE-DTDE)
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
/*
(DTATE-DTDE)
----------------------
1,00069444444444444444444444444444444444
0,000694444444444444444444444444444444444444
0,0607291666666666666666666666666666666667
0,0155324074074074074074074074074074074074
*/
--EM SEGUNDOS
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
/*
X
----------------------
86459,9999999999999999999999999999999996
59,99999999999999999999999999999999999996
5247,000000000000000000000000000000000003
1341,999999999999999999999999999999999999
*/
--MEDIA
select AVG(X)
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)
/*
AVG(X)
----------------------
23277,2499999999999999999999999999999999
*/
--MEDIA EM DIAS
select (AVG(X))/(24*60*60) S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)
/*
S
----------------------
0,2694126157407407407407407407407407407396
*/
--FORCA UM DIA ZERADO E SOMA ESTES DIAS
select TO_DATE('01/01/2013 00:00:00','DD/MM/YYYY HH24:MI:SS') + (AVG(X))/(24*60*60) S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)
/*
S
-------------------------
01/01/2013 06:27:57
*/
--CONVERTANDO EM HORA
select TO_CHAR(TO_DATE('01/01/2013 00:00:00','DD/MM/YYYY HH24:MI:SS') + (AVG(X))/(24*60*60),'HH24:MI:SS') S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)
/*
S
-----------
06:27:57
*/
Este exemplo se limita a médias inferiores à um dia, mas isto pode ser facilmente resolvido, para o obter o dia é subtrair 1, pois o artifício parte do dia "1".
