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
| WITH MaTable AS
(
SELECT 'Natation' AS Activite, to_date('2012/10/24 08:10','yyyy/mm/dd hh24:mi') AS Date_debut, to_date('2012/10/24 10:10','yyyy/mm/dd hh24:mi') AS Date_fin, 2 AS Priorite, 102030 num_sportif FROM dual union ALL
SELECT 'Judo' , to_date('2012/10/24 11:11','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 12:00','yyyy/mm/dd hh24:mi') , 1 , 102030 FROM dual union ALL
SELECT 'Lecture' , to_date('2012/10/24 09:00','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 11:10','yyyy/mm/dd hh24:mi') , 3 , 102030 FROM dual union ALL
SELECT 'Jogging' , to_date('2012/10/24 17:30','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 18:30','yyyy/mm/dd hh24:mi') , 4 , 102030 FROM dual union ALL
SELECT 'Natation' , to_date('2012/10/25 08:10','yyyy/mm/dd hh24:mi') , to_date('2012/10/25 10:10','yyyy/mm/dd hh24:mi') , 2 , 102030 FROM dual union ALL
SELECT 'Jogging' , to_date('2012/10/25 17:30','yyyy/mm/dd hh24:mi') , to_date('2012/10/25 18:30','yyyy/mm/dd hh24:mi') , 4 , 102030 FROM dual union ALL
SELECT 'Natation' , to_date('2012/10/24 10:10','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 12:10','yyyy/mm/dd hh24:mi') , 2 , 102031 FROM dual union ALL
SELECT 'Judo' , to_date('2012/10/24 11:00','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 13:00','yyyy/mm/dd hh24:mi') , 1 , 102031 FROM dual union ALL
SELECT 'Lecture' , to_date('2012/10/24 15:10','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 17:10','yyyy/mm/dd hh24:mi') , 3 , 102031 FROM dual union ALL
SELECT 'Jogging' , to_date('2012/10/24 17:30','yyyy/mm/dd hh24:mi') , to_date('2012/10/24 18:30','yyyy/mm/dd hh24:mi') , 4 , 102031 FROM dual union ALL
SELECT 'Natation' , to_date('2012/10/25 08:10','yyyy/mm/dd hh24:mi') , to_date('2012/10/25 10:10','yyyy/mm/dd hh24:mi') , 2 , 102031 FROM dual union ALL
SELECT 'Jogging' , to_date('2012/10/25 17:30','yyyy/mm/dd hh24:mi') , to_date('2012/10/25 18:30','yyyy/mm/dd hh24:mi') , 4 , 102031 FROM dual
)
, Tab_deb AS
(
SELECT DISTINCT trunc(Date_debut) AS dt_deb FROM MaTable
)
, Tab_maj AS
(
SELECT t.Activite,
case when t.Priorite > lag(t.Priorite) over (partition by t.num_sportif order by t.Date_debut)
and t.Date_debut < lag(t.Date_fin) over (partition by t.num_sportif order by t.Date_debut)
then lag(t.Date_fin) over (partition by t.num_sportif order by t.Date_debut)+1/24/60
else t.Date_debut
end as Date_debut,
t.date_fin, t.Priorite, t.num_sportif
from matable t
)
select * from tab_maj |
Partager