1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| SQL> with data_exemple as (
2 select 1 as pk, to_date('03/04/2014','DD/MM/YYYY') as date_deb, to_date('07/04/2014','DD/MM/YYYY') as date_fin from dual union all
3 select 2 as pk, to_date('06/05/2014','DD/MM/YYYY') as date_deb, to_date('27/05/2014','DD/MM/YYYY') as date_fin from dual
4 ),
5 liste_date as (
6 select t.*, to_char(date_deb + rownum, 'FMDAY', 'NLS_DATE_LANGUAGE=FRENCH') as date_jour
7 from data_exemple t
8 connect BY level <= t.date_fin - t.date_deb + 1
9 AND PRIOR pk = pk
10 AND prior sys_guid() IS NOT NULL
11 )
12 select date_deb, date_fin, sum(case when date_jour in ('SAMEDI', 'DIMANCHE') then 1 end) as nb_jour_we
13 from liste_date
14 group by date_deb, date_fin;
DATE_DEB DATE_FIN NB_JOUR_WE
-------- -------- ----------
06/05/14 27/05/14 6
03/04/14 07/04/14 2
SQL> |
Partager