Gurus,
For a long time I was searching for a solution, but without success. I'm sure here it will come up.
My problem is that I have some production order (OP) and want to sum the duration time (DURACAO - in hours) while color (COR) is the same, grouped by machine code (RECURSO) and ordered by start timestamp (DATA_PROG_INI).
create table t1 (RECURSO number, data_prog_ini number, duração number, cor varchar2(20))
/
insert into t1 values (303,42737.2222222222,16.8,'PRETO');
insert into t1 values (303,42738.0625,115.1667,'CRISTAL');
insert into t1 values (303,42744.2222222222,51.6,'CRISTAL');
insert into t1 values (303,42746.3402777778,84.55,'CRISTAL');
insert into t1 values (303,42751.2222222222,173.7667,'CRISTAL');
insert into t1 values (303,42758.2916666667,52,'CRISTAL');
insert into t1 values (303,42760.4583333333,81.6,'CRISTAL');
insert into t1 values (303,42765.3791666667,182.9,'CRISTAL');
insert into t1 values (303,42773,93.3333,'CRISTAL');
insert into t1 values (303,42776.8888888889,51.25,'CRISTAL');
insert into t1 values (306,42737.2222222222,0.5167,'PRETO');
insert into t1 values (306,42737.24375,3.2,'PRETO');
insert into t1 values (306,42737.3770833333,3.2,'CRISTAL');
insert into t1 values (306,42737.5104166667,3.4833,'CRISTAL');
insert into t1 values (306,42737.6555555556,5.5833,'BRANCO');
insert into t1 values (306,42737.8881944444,22.3667,'BRANCO');
insert into t1 values (306,42738.8402777778,25.1,'BRANCO');
insert into t1 values (306,42739.8333333333,6.3833,'BRANCO');
insert into t1 values (306,42740.0993055556,0.2833,'CRISTAL');
insert into t1 values (306,42740.2291666667,22.3167,'CRISTAL');
insert into t1 values (306,42741.1590277778,3.2,'BRANCO');
insert into t1 values (306,42741.3194444444,22.3167,'BRANCO');
insert into t1 values (306,42742.2493055556,3.2,'BRANCO');
insert into t1 values (306,42742.3826388889,44.15,'BRANCO');
insert into t1 values (306,42744.2222222222,2.4333,'PRETO');
insert into t1 values (306,42744.3236111111,6.2167,'CRISTAL');
insert into t1 values (306,42744.5826388889,13.95,'CRISTAL');
insert into t1 values (306,42745.1638888889,19.1667,'BRANCO');
insert into t1 values (306,42745.9625,25,'BRANCO');
insert into t1 values (306,42747.0041666667,25.5667,'BRANCO');
insert into t1 values (306,42748.0666666667,24.4,'BRANCO');
insert into t1 values (306,42749.1111111111,51.4167,'BRANCO');
I would like to have an output like this:
RECURSO DURAÇÃO COR
303 16,80 PRETO
303 886,17 CRISTAL
306 3,72 PRETO
306 6,68 CRISTAL (6,68 was edited: in original post it was 3,72)
306 59,43 BRANCO
306 22,60 CRISTAL
306 72,87 BRANCO
306 2,43 PRETO
306 20,17 CRISTAL
306 145,55 BRANCO
Every time that color(COR) change in the same machine (RECURSO), sum(DURAÇÃO) must restart.
Thanks,
Henrique
Using Oracle Database 11g Release 11.2.0.3.0 - 64bit Production