Discussion :

[Aurel2340]

Bonjour,

A partir d'une date de début et une date de fin, j'obtiens une durée (en secondes). Je souhaiterais répartir cette durée sur un calendrier. En effet, j'ai une ligne avec une date de début jour J et la date de fin qui peut parfois être sur le J+1 (ou J+2 etc), et je souhaiterais récupérer la durée répartie sur les jours du calendrier.

Exemple :
Voici mes données :


Et je voudrais obtenir le résultat suivant :


Auriez-vous une idée de comment faire svp ?

Merci !

[skuatamad]

La question n'est pas tout à fait assez précise :

Si on ajoute les données suivantes :

21/01/2022	18:00	21/01/2022	18:01	60
19/01/2022	05:00	19/01/2022	05:01	60
22/01/2022	05:00	22/01/2022	05:01	60

Quel devra être le résultat :

19/01	60
20/01	0 <= Cette ligne doit elle être générée ?
21/01	34260 <= Doit on faire la somme sur la journée ?
22/01	3000 (2940 + 60) <= Doit on faire la somme sur la journée ?
23/01	7800

Ou alors, on ne somme pas et on affiche plusieurs fois les journées (probablement avec d'autres infos pour les différencier):

21/01	34200
21/01	60
22/01	2940
22/01	60

[Aurel2340]

Bonjour skuatamad,

Merci pour votre réponse.
En effet, je n'ai pas trop détaillé car je pensais me débrouiller pour le reste.

Si on ajoute vos données, le résultat serait le suivant :

DATE	DUREE
19.01	60
20.01	0
21.01	60
22.01	3000
23.01	7800

On somme donc pour la même journée mais j'ai aussi une colonne statut.... Je somme les durées pour le même statut et je pourrai donc aussi avoir 2 lignes pour une même date si le statut est différent.... :

DATE	DUREE	STATUT
19.01	60	ACTIF
20.01	0	INACTIF
21.01	60	ACTIF
22.01	3000	ACTIF
22.01	60	INACTIF
23.01	7800	ACTIF

Merci.

[skuatamad]

Avec ce jeux de données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
with t as (
select to_date('19/01/2022 05:00', 'dd/mm/yyyy hh24:mi') as debut, to_date('19/01/2022 05:01', 'dd/mm/yyyy hh24:mi') as fin from dual union all
select to_date('21/01/2022 18:00', 'dd/mm/yyyy hh24:mi') as debut, to_date('21/01/2022 18:01', 'dd/mm/yyyy hh24:mi') as fin from dual union all
select to_date('22/01/2022 05:00', 'dd/mm/yyyy hh24:mi') as debut, to_date('22/01/2022 05:01', 'dd/mm/yyyy hh24:mi') as fin from dual union all
select to_date('21/01/2022 07:30', 'dd/mm/yyyy hh24:mi') as debut, to_date('21/01/2022 17:00', 'dd/mm/yyyy hh24:mi') as fin from dual union all
select to_date('22/01/2022 23:11', 'dd/mm/yyyy hh24:mi') as debut, to_date('23/01/2022 02:10', 'dd/mm/yyyy hh24:mi') as fin from dual union all
select to_date('28/01/2022 22:11', 'dd/mm/yyyy hh24:mi') as debut, to_date('01/02/2022 03:10', 'dd/mm/yyyy hh24:mi') as fin from dual
)

Ça pourrait donner :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
  with periode as (
select trunc(min(debut)) as dt_deb, trunc(max(fin)) as dt_fin 
  from t
)
     , calendrier as (	 
select dt_deb + rownum - 1 as dte
  from periode
connect by level <= dt_fin - dt_deb + 1
)
     , decoupage_par_jour as (	   
select c.dte
       -- Si le jour J est celui de la date de début on récupère la date début pour avoir l'heure de début sinon on est sur un jour suivant donc jour J à minuit
     , case when trunc(t.debut) = trunc(c.dte) then t.debut else c.dte end as deb_par_jour 
	   -- Si le jour J est celui de la date de fin alors on prend la date de fin pour avoir l'heure de fin sinon la fin est le lendemain du jour J
     , case when trunc(t.fin)   = trunc(c.dte) then t.fin   else c.dte + 1 end as fin_par_jour
  from calendrier c
       -- Pas de jointure externe pour traiter dans un 1er temps uniquement les jours concernés pour le calcul des durées
  join t on c.dte between trunc(t.debut) and trunc(t.fin)
)
select to_char(c2.dte,'dd/mm/yyyy') as jour
     , round(nvl(sum(d.fin_par_jour - d.deb_par_jour), 0)*86400) as duree_par_jour
  from calendrier c2
       -- Jointure externe pour inclure les jours sans données
  left join decoupage_par_jour d on d.dte = c2.dte
 group by c2.dte
 order by c2.dte

;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
JOUR       DUREE_PAR_JOUR
---------- --------------
19/01/2022             60
20/01/2022              0
21/01/2022          34260
22/01/2022           3000
23/01/2022           7800
24/01/2022              0
25/01/2022              0
26/01/2022              0
27/01/2022              0
28/01/2022           6540
29/01/2022          86400
30/01/2022          86400
31/01/2022          86400
01/02/2022          11400
 
14 rows selected.

Pour le statut, il suffira probablement de le rajouter dans le GROUP BY

[JeitEmgie]

Vous pouvez supprimer les CASE WHEN en utilisant GREATEST et LEAST et en retournant 2 valeurs du CTE calendrier : le début du jour (cdebut) et celui du jour + 1 (cfin),
la durée en secondes est alors calculée ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
	ROUND((24*3600)*( GREATEST(0, LEAST(cfin, d.fin) - GREATEST(d.debut,cdebut)) ),0) AS duree

la jointure entre les data et le calendrier étant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
	JOIN calendar c ON debut < cfin AND fin >= cdebut

[Aurel2340]

Bonjour,

J'ai essayé de tout mettre en une fois dans un select :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
select to_char(CALENDRIER.DATE_ETAT,'dd/mm/yyyy') DATE_ETAT
     , round(nvl(sum(DECOUPAGE_PAR_J.fin_par_jour - DECOUPAGE_PAR_J.deb_par_jour), 0)*86400) DUREE
FROM (SELECT PERIODE.DATE_DEBUT + rownum - 1 DATE_ETAT
      FROM (SELECT TRUNC(MIN(TO_DATE (DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS'))) DATE_DEBUT, 
                   TRUNC(MAX(TO_DATE (DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DATE_FIN
            FROM DATA IND) PERIODE
       connect by level <= PERIODE.DATE_FIN - PERIODE.DATE_DEBUT + 1) CALENDRIER,
       (SELECT CALENDRIER.DATE_ETAT,
       CASE WHEN TRUNC(PERIODE.DATE_DEBUT) = trunc(CALENDRIER.DATE_ETAT) 
            then PERIODE.DATE_DEBUT 
            else CALENDRIER.DATE_ETAT 
       end deb_par_jour, 
       -- Si le jour J est celui de la date de fin alors on prend la date de fin pour avoir l'heure de fin sinon la fin est le lendemain du jour J
       case when trunc(PERIODE.DATE_FIN) = trunc(CALENDRIER.DATE_ETAT) 
            then PERIODE.DATE_FIN   
            else CALENDRIER.DATE_ETAT + 1 
       end fin_par_jour
FROM (SELECT PERIODE.DATE_DEBUT + rownum - 1 DATE_ETAT
      FROM (SELECT TRUNC(MIN(TO_DATE (DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS'))) DATE_DEBUT, 
                   TRUNC(MAX(TO_DATE (DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DATE_FIN
            FROM DATA IND) PERIODE
       connect by level <= PERIODE.DATE_FIN - PERIODE.DATE_DEBUT + 1) CALENDRIER,
      (SELECT TRUNC(MIN(TO_DATE (DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS'))) DATE_DEBUT, 
              TRUNC(MAX(TO_DATE (DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DATE_FIN
       FROM DATA IND)PERIODE
WHERE CALENDRIER.DATE_ETAT BETWEEN TRUNC(PERIODE.DATE_DEBUT) AND trunc(PERIODE.DATE_FIN)) DECOUPAGE_PAR_J
WHERE DECOUPAGE_PAR_J.DATE_ETAT(+) = CALENDRIER.DATE_ETAT
 group by CALENDRIER.DATE_ETAT
 order by CALENDRIER.DATE_ETAT

mais il doit y avoir un souci quelque part car j'obtiens ceci....... :

19/01/2022	86400
20/01/2022	86400
21/01/2022	86400
22/01/2022	86400
23/01/2022	86400
24/01/2022	86400
25/01/2022	86400
26/01/2022	86400
27/01/2022	86400
28/01/2022	86400
29/01/2022	86400
30/01/2022	86400
31/01/2022	86400
01/02/2022	0

Merci !

[skuatamad]

Je ne comprends pas ce que vous avez fait. Il suffit de prendre la requête avec le WITH et de remplacer T par le nom de votre table ainsi que les colonnes début et fin.

Si vos dates sont bien de type de données date, il ne faut pas les reconvertir en date avec to_date.

[Aurel2340]

Justement non, mes données sont de type string à la base....

Si cela peut servir, voici mon code final qui fonctionne tip top !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
select DISTINCT
       CALENDRIER_ETAT.DATE,
       STATUT,
       DECOUPAGE_JOUR.deb_par_jour,
       DECOUPAGE_JOUR.fin_par_jour,
       round(nvl(sum(DECOUPAGE_JOUR.fin_par_jour - DECOUPAGE_JOUR.deb_par_jour), 0)*86400) DUREE
  from (SELECT DISTINCT CALENDRIER.DATE,
       case when TRUNC(TO_DATE (IND.DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS')) = trunc(CALENDRIER.DATE) 
            then TO_DATE (IND.DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS')
            else TO_DATE(TO_CHAR(CALENDRIER.DATE,'DD.MM.YYYY')|| '00:00:01','DD.MM.YYYY HH24:MI:SS') 
       end deb_par_jour,
       case when TRUNC(TO_DATE (IND.DATE_FIN,'DD/MM/YYYY HH24:MI:SS')) = trunc(CALENDRIER.DATE) 
            then TO_DATE (IND.DATE_FIN,'DD/MM/YYYY HH24:MI:SS')
            else TO_DATE(TO_CHAR(CALENDRIER.DATE,'DD.MM.YYYY')|| '23:59:59','DD.MM.YYYY HH24:MI:SS') 
       end fin_par_jour,
       IND.STATUT
FROM (SELECT DISTINCT PERIODE.DATE_DEBUT + rownum - 1 DATE
      FROM (SELECT TRUNC(MIN(TO_DATE (DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS'))) DATE_DEBUT, 
                   TRUNC(MAX(TO_DATE (DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DATE_FIN
            FROM DATA IND) PERIODE
      connect by level <= PERIODE.DATE_FIN - PERIODE.DATE_DEBUT + 1) CALENDRIER,
     DATA IND
WHERE CALENDRIER.DATE between TRUNC(TO_DATE (IND.DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS')) and TRUNC(TO_DATE (IND.DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DECOUPAGE_JOUR,
(SELECT DISTINCT PERIODE.DATE_DEBUT + rownum - 1 DATE
FROM (SELECT TRUNC(MIN(TO_DATE (DATE_DEBUT,'DD/MM/YYYY HH24:MI:SS'))) DATE_DEBUT, 
             TRUNC(MAX(TO_DATE (DATE_FIN,'DD/MM/YYYY HH24:MI:SS'))) DATE_FIN
      FROM DATA IND) PERIODE
connect by level <= PERIODE.DATE_FIN - PERIODE.DATE_DEBUT + 1) CALENDRIER_ETAT
       -- Jointure externe pour inclure les jours sans données
WHERE CALENDRIER_ETAT.DATE = DECOUPAGE_JOUR.DATE(+)
group by CALENDRIER_ETAT.DATE, STATUT, DECOUPAGE_JOUR.fin_par_jour,
       DECOUPAGE_JOUR.deb_par_jour
ORDER BY CALENDRIER_ETAT.DATE,
         DECOUPAGE_JOUR.deb_par_jour

Merci beaucoup pour votre aide !

[skuatamad]

Mais pourquoi vous n'utilisez pas le WITH ?

C'est nettement plus lisible, plus simple à tester et plus réutilisable (le calendrier est généré une fois et utilisé 2 fois)

[Aurel2340]

Cela ne fonctionne pas dans mon éditeur :