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
|
mni@DIANA> with pivot as (
2 Select level x from dual connect by level <= 10
3 ), Data as (
4 Select 1 id, sysdate datdeb, sysdate + 7*3 datfin from dual union all
5 Select 2 id, sysdate datdeb, sysdate + 7*2 datfin from dual
6 )
7 Select id, datdeb, x, round(x/2),
8 datdeb + (round(x/2)-1)*7 + case when mod(x,2) = 1 Then 0 Else 4 End debper,
9 datdeb + (round(x/2)-1)*7 + case when mod(x,2) = 1 Then 3 Else 6 End finper
10 from data, pivot
11 where x <= (datfin-datdeb)/7*2
12 order by id, x
13 /
ID DATDEB X ROUND(X/2) DEBPER FINPER
---------- -------- ---------- ---------- -------- --------
1 19/03/09 1 1 19/03/09 22/03/09
1 19/03/09 2 1 23/03/09 25/03/09
1 19/03/09 3 2 26/03/09 29/03/09
1 19/03/09 4 2 30/03/09 01/04/09
1 19/03/09 5 3 02/04/09 05/04/09
1 19/03/09 6 3 06/04/09 08/04/09
2 19/03/09 1 1 19/03/09 22/03/09
2 19/03/09 2 1 23/03/09 25/03/09
2 19/03/09 3 2 26/03/09 29/03/09
2 19/03/09 4 2 30/03/09 01/04/09
10 ligne(s) sÚlectionnÚe(s). |
Partager