1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| With TABLE1 (ID, IDVOITURE, DATESAISIE, DATEDEBUT, DATEFIN, PUISSANCE, TYPESAISIE) as
(
select 1, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:45', 'dd/mm/yyyy hh24:mi'), 540, 2 from dual union all
select 2, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), 530, 2 from dual union all
select 3, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), 520, 2 from dual union all
select 4, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'), 510, 2 from dual union all
select 5, 20, to_date('21/05/2013 09:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 12:00', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 15:00', 'dd/mm/yyyy hh24:mi'), 200, 3 from dual union all
select 6, 20, to_date('21/05/2013 08:56:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:45', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 23:55', 'dd/mm/yyyy hh24:mi'), 263, 4 from dual union all
select 7, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:30', 'dd/mm/yyyy hh24:mi'), 152, 6 from dual union all
select 8, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:20', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:25', 'dd/mm/yyyy hh24:mi'), 150, 6 from dual union all
select 9, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:15', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:20', 'dd/mm/yyyy hh24:mi'), 149, 6 from dual union all
select 10, 22, to_date('21/05/2013 19:36:15', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 20:00', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 22:00', 'dd/mm/yyyy hh24:mi'), 800, 4 from dual union all
select 11, 23, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:45', 'dd/mm/yyyy hh24:mi'), 540, 2 from dual union all
select 12, 23, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), 530, 2 from dual union all
select 13, 20, to_date('21/05/2013 22:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 00:30', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 12:35', 'dd/mm/yyyy hh24:mi'), 255, 2 from dual union all
select 14, 20, to_date('21/05/2013 14:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'), 60, 2 from dual union all
select 15, 20, to_date('21/05/2013 13:00:16', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 09:15', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 09:50', 'dd/mm/yyyy hh24:mi'), 10, null from dual
)
select max(id) as id
, idvoiture
, datesaisie
, min(datedebut) as datedebut
, max(datefin) as datefin
, max(puissance) keep(dense_rank first order by datefin desc) as puissance
, typesaisie
from Table1
group by idvoiture
, datesaisie
, typesaisie
order by 1 asc;
ID IDVOITURE DATESAISIE DATEDEBUT DATEFIN PUISSANCE TYPESAISIE
-- --------- ------------------- ---------------- ---------------- --------- ----------
4 20 2013-05-21 10:12:05 2013-05-21 10:25 2013-05-21 10:45 540 2
5 20 2013-05-21 09:00:05 2013-05-21 12:00 2013-05-21 15:00 200 3
6 20 2013-05-21 08:56:05 2013-05-21 21:45 2013-05-21 23:55 263 4
9 21 2013-05-21 21:00:05 2013-05-21 21:15 2013-05-21 21:30 152 6
10 22 2013-05-21 19:36:15 2013-05-21 20:00 2013-05-21 22:00 800 4
12 23 2013-05-21 10:12:05 2013-05-21 10:35 2013-05-21 10:45 540 2
13 20 2013-05-21 22:12:05 2013-05-21 00:30 2013-05-21 12:35 255 2
14 20 2013-05-21 14:12:05 2013-05-21 10:25 2013-05-21 10:30 60 2
15 20 2013-05-21 13:00:16 2013-05-21 09:15 2013-05-21 09:50 10 |
Partager