I have the following command:
select substr(b.chave, 28, 7) numeronf, upper(d.apelido) usuario, decode(b.loja,1,'Loja 1', 2,'CD', 3, 'Loja 2', 5,'Loja 3', 89, 'PANIF', 90, 'Restaurante' ) loja, c.nomerazao, a.data_inicial, a.data_final,
to_char(trunc(( (data_final - a.data_inicial) * 86400 / 3600)),'00') ||':' ||
to_char(trunc(mod( (data_final - a.data_inicial) * 86400 , 3600 ) / 60 ),'00') || ':'||
to_char(trunc(mod ( mod ( (data_final - a.data_inicial) * 86400, 3600 ), 60 )),'00') hora
from tabela1 a, tabela2 b, tabela3 c, tabela d
where a.id_chave = b.id_chave
and a.usuario = d.matricula
and a.usuario = $digitador
and a.status = 'LN'
and c.nrocgccpf = substr(b.chave, 7, 12)
and c.digcgccpf = substr(b.chave, 19, 2)
and trunc(a.data_inicial) between '$data_inicial' and '$data_final'
order by a.data_inicial
How the table looks:
+------------+-----------+----------+-------+----------+----------+
| Loja | Data |Fornecedor| NumNF | Dgitador |Hora |
+------------+-----------+----------+-------+----------+----------+
| 01 |2014-01-02 | Fulano | 000000| Fulano |00: 06: 37|
| 01 |2014-02-07 | Fulano | 000000| Fulano |00: 06: 37|
| 01 |2014-02-16 | Ciclano | 000000| Fulano |00: 06: 37|
| 02 |2014-01-20 | Fulano | 000000| Fulano |00: 06: 37|
| 02 |2014-03-05 | Fulano | 000000| Fulano |00: 06: 37|
| 03 |2014-03-12 | Ciclano | 000000| Fulano |00: 06: 37|
| 03 |2014-07-25 | Ciclano | 000000| Fulano |00: 06: 37|
+------------+-----------+----------+-------+----------+----------+
How do I add the values in the hour column?