Calcul par tranche de 30 min entre deux dates

**pcano** · 01/04/2019, 09h15

Bonjour,

Je ne sais pas par quel bout attaquer ce problème là, aussi je sollicite un peu d'aide. J'ai une requête SQL qui me renvoie les données sous cette forme (simplifiée):

Intervention | Date-Heure début | Date-Heure fin | Matricule Agent | Nom Prénom
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 123 | Jon Doe
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 212 | Johnny English
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 411 | Pierre Dupond
1021 | 01/01/2018 09:17 | 01/01/2018 10:20 | 520 | Tinitin Milou
1021 | 01/01/2018 09:17 | 01/01/2018 10:20 | 732 | Edouard Leclerc
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 123 | Jon Doe
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 212 | Johnny English
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 411 | Pierre Dupond

Ce que je souhaiterais pouvoir savoir c'est le nombre de personne en intervention par tranche de 30 min. (peu importe le format d"expression de la tranche, date, numéro, minutes, timestamp ...).
Dans l'exemple ci dessous cela donnerait :

Tranche | Nbre d'intervenant
le 01/01/2018 de 08:00 à 08:29 | 0
le 01/01/2018 de 08:30 à 08:59 | 3
le 01/01/2018 de 09:00 à 09:29 | 5
le 01/01/2018 de 09:30 à 09:59 | 5
le 01/01/2018 de 10:00 à 10:29 | 2
le 01/01/2018 de 10:30 à 10:59 | 3
le 01/01/2018 de 11:00 à 11:29 | 3
le 01/01/2018 de 11:30 à 11:59 | 3

De plus, je dois pouvoir avoir ça sur toute une année, qui compte environ 18 000 interventions avec 2 à 6 participants par interventions.

Une idée de comment faire ?

Merci!

**escartefigue** · 01/04/2019, 11h50

bonjour,

Il faut construire une table des tranches horaires puis faire une jointure avec cette table :

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
With Htab(Hseq, Hdeb, Hfin) as
    (select 1
          , cast('00:00:00' as time)
          , cast('00:29:59' as time)   
     union all
     select Hseq+1
          , Hdeb+30 minutes 
          , Hfin+30 minutes
     from Htab
     where Hseq < 48)
   , Tab1 (Xidt, Xnom, XTst) as
    (
     select 004, 'Tintin', cast('2019-04-01 00:05:16.000000' as timestamp)   
     union all
     select 005, 'Dupont', cast('2019-04-01 01:12:15.000000' as timestamp)   
     union all
     select 006, 'Durand', cast('2019-04-01 01:20:32.000000' as timestamp)   
     union all
     select 007, 'Martin', cast('2019-04-01 01:40:06.000000' as timestamp)   
     union all
     select 008, 'Tintin', cast('2019-04-01 02:09:25.000000' as timestamp)   
     union all
     select 009, 'Tagada', cast('2019-04-01 02:09:25.000000' as timestamp)   
     union all
     select 010, 'ABCDEF', cast('2019-04-01 02:09:25.000000' as timestamp)   
    )
 
select Hdeb
     , Hfin
     , count(distinct xnom)
from Htab
inner join Tab1
   on cast(substr(char(Xtst), 12, 8) as time) between Hdeb and Hfin    
group by Hdeb, Hfin

On obtient :

Code :

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

1
2
3
4
5
6
7
 
HDEB      HFIN      3            
---------------------------------
00:00:00  00:29:59  1            
01:00:00  01:29:59  2            
01:30:00  01:59:59  1            
02:00:00  02:29:59  3

Il faut ajouter une restriction sur la date (dans mon jeu d'essai, toutes les lignes sont sur le même jour)
et bien sur faire un regroupement sur l'identifiant de la personne et pas sur le nom, les homonymes étant possibles, là j'ai fait une solution à la va vite, mais le principe reste le même

**pcano** · 01/04/2019, 18h29

Bonjour,

Merci pour cette première approche, mais j'ai l'impression que la requête est basée sur une date-heure fixe par personne. Par exemple 'Tintin' à '2019-04-01 00:05:16'.
Or dans mon cas Tintin intervient de 00:05:16 à 01:45:00 et donc doit être comptabilisé :
sur la tranches 00:00 à 00:29:59
mais aussi
sur les tranches suivantes: 00:30 à 00:59:59 , 01:00 à 01:29:59 et 01:30 à 01:59:59

Une idée ?

Merci.

**Waldar** · 02/04/2019, 18h24

Quel est votre SGBD ? Le support des périodes n'est pas la chose la plus répandue ni la plus homogène dans les différents dialectes.

**pcano** · 03/04/2019, 11h18

Bonjour,

C'est de l'oracle 10G

Cdlt

**Waldar** · 03/04/2019, 14h19

Essayez ainsi :

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Données
create table pcano
( Intervention  smallint
, dt_deb        date
, dt_fin        date
, id_agent      smallint
, nm_agent      varchar2(20)
);
 
insert all
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 123, 'Jon Doe')
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 212, 'Johnny English')
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 411, 'Pierre Dupond')
    into pcano values (1021, to_date('01/01/2018 09:17', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 10:20', 'dd/mm/yyyy hh24:mi'), 520, 'Tinitin Milou')
    into pcano values (1021, to_date('01/01/2018 09:17', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 10:20', 'dd/mm/yyyy hh24:mi'), 732, 'Edouard Leclerc')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 123, 'Jon Doe')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 212, 'Johnny English')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 411, 'Pierre Dupond')
select * from dual;
 
 
-- Requête
with cte_bornes (dt_min, dt_max) as
(
select trunc(min(dt_deb), 'dd') + (floor((min(dt_deb) - trunc(min(dt_deb), 'dd'))*48)-1)/48
     , trunc(max(dt_fin), 'dd') + (floor((max(dt_fin) - trunc(max(dt_fin), 'dd'))*48)+1)/48
  from pcano
)
  ,  cte_period (per_min, per_max) as
(
    select dt_min + (level - 1) * 30/60/24
         , dt_min + level       * 30/60/24
      from cte_bornes
connect by level <= (dt_max - dt_min)*48
)
    select per.per_min, per.per_max
         , count(tbl.Intervention)
      from cte_period per
 left join pcano      tbl on (tbl.dt_deb, tbl.dt_fin) overlaps (per.per_min, per.per_max)
  group by per.per_min, per.per_max
  order by per.per_min;
 
PER_MIN              PER_MAX              COUNT(TBL.INTERVENTION)
-------------------  -------------------  -----------------------
2018-01-01 08:00:00  2018-01-01 08:30:00                        0
2018-01-01 08:30:00  2018-01-01 09:00:00                        3
2018-01-01 09:00:00  2018-01-01 09:30:00                        5
2018-01-01 09:30:00  2018-01-01 10:00:00                        5
2018-01-01 10:00:00  2018-01-01 10:30:00                        2
2018-01-01 10:30:00  2018-01-01 11:00:00                        3
2018-01-01 11:00:00  2018-01-01 11:30:00                        3
2018-01-01 11:30:00  2018-01-01 12:00:00                        3

**Michel.Priori** · 03/04/2019, 15h07

Ben, la réponse d'escartefique me semble tout à fait pertinente !
Il suffit de s'en inspirer.

Il me semble qu'Oracle 10g il n'existe pas encore les CTE.

En s'inspirant de la solution de Waldar, il me semble aussi que l'opérateur Overlap n'est pas non plus disponible en 10g.

Du coup la clause FROM devrait ressembler à

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
FROM
(
    select dt_min + (level - 1) * 30/60/24 as per_min
         , dt_min + level       * 30/60/24 as per_max
      from 
			(
			select trunc(min(dt_deb), 'dd') + (floor((min(dt_deb) - trunc(min(dt_deb), 'dd'))*48)-1)/48 as dt_min
				 , trunc(max(dt_fin), 'dd') + (floor((max(dt_fin) - trunc(max(dt_fin), 'dd'))*48)+1)/48 as dt_max
			  from pcano
			)cte_bornes	  
connect by level <= (dt_max - dt_min)*48
)cte_period
inner join pcano 
	on pcano.dt_deb < cte_period.per_max
	and pcano.dt_fin > cte_period.per_min

Calcul par tranche de 30 min entre deux dates

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager