Afficher les dates à partir d'une durée

Version imprimable

02/02/2022, 10h24
Aurel2340

4 pièce(s) jointe(s)

Afficher les dates à partir d'une durée

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 :
Pièce jointe 612915

Et je voudrais obtenir le résultat suivant :
Pièce jointe 612916

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

Merci !

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

02/02/2022, 11h40
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.

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

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

Avec ce jeux de données :

Code:

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:

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:

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

02/02/2022, 14h57
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:

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:

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

Bonjour,

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

Code:

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 !

03/02/2022, 14h30
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.

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

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

Code:

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 !

03/02/2022, 15h18
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)
03/02/2022, 15h35
Aurel2340

1 pièce(s) jointe(s)

Cela ne fonctionne pas dans mon éditeur :
Pièce jointe 613014

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

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