Manipulation de données sur des dates

**kekesar** · 20/07/2020, 14h14

Bonjour à tous,
Je viens à vous aujourd'hui car je me retrouve face à un mur.

je vous explique, j'ai cette requête qui m'affiche toute les prochaines dates de formation ainsi que les formateur sur le site en question :

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
SELECT
	DISTINCT concat(CT.NOM,concat( ' ', CT.Prenom)) AS NAME,
	DATERDV,
	ST.NOM 
FROM
	planning pg 
		INNER JOIN CODIF CD 
		ON CD.ID_CODIF = PG.REF_TYPEEVEN 
			INNER JOIN CONTACT CT 
			ON CT.ID_CONTACT = PG.REF_CONCERNE 
				INNER JOIN SITE ST 
				ON ST.ID_SITE = PG.REF_SITE 
WHERE
	pg.REF_TYPEEVEN IN (3,
	25,
	30,
	31) AND
	DATERDV > CURRENT_DATE AND
	VALEUR LIKE '%FORMATION%' 
ORDER BY
	DATERDV

Ayant pour résultat ce genre de chose :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Toto Tata	25/08/2020 00:00:00	Site B
Toto Tata	26/08/2020 00:00:00	Site B
Toto Tata   27/08/2020 00:00:00	Site B
Toto Tata	28/08/2020 00:00:00	Site B
Toto Tata	31/08/2020 00:00:00	Site B
Toto Tata	01/09/2020 00:00:00	Site B
Toto Tata   02/09/2020 00:00:00	Site B
Eric Toto	03/09/2020 00:00:00	GRENOBLE
Maillot toto	03/09/2020 00:00:00	Site B
Maillot toto 04/09/2020 00:00:00	Site B

Je souhaiterais comment je pourrais me retrouver avec des lignes du genre :

Code :

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

1
2
3
Toto Tata 25/08/2020 02/09/2020 Site B
Eric Toto	03/09/2020 03/09/2020 GRENOBLE
Maillot toto	03/09/2020 04/09/2020	Site B

En gros regrouper si il y a plusieurs date se suivant d'un même client et d'un même formateur au format NOM_FORMATEUR / Date_Debut / Date_FIN / Client.

Merci à tous.

**vanagreg** · 20/07/2020, 14h42

Bonjour,

Quelle est la version de ta base de données?

S'il n'y a pas de "trous" entre les dates alors simplement:

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
SELECT
	concat(CT.NOM,concat( ' ', CT.Prenom)) AS NAME,   
	min(DATERDV) date_debut,
        max(dateredv) date_fin,     
	ST.NOM 
FROM
	planning pg 
		INNER JOIN CODIF CD 
		ON CD.ID_CODIF = PG.REF_TYPEEVEN 
			INNER JOIN CONTACT CT 
			ON CT.ID_CONTACT = PG.REF_CONCERNE 
				INNER JOIN SITE ST 
				ON ST.ID_SITE = PG.REF_SITE 
WHERE
	pg.REF_TYPEEVEN IN (3,
	25,
	30,
	31) AND
	DATERDV > CURRENT_DATE AND
	VALEUR LIKE '%FORMATION%' 
GROUP BY concat(CT.NOM,concat( ' ', CT.Prenom)),     
         ST.NOM 
ORDER BY 1, 2;

**skuatamad** · 20/07/2020, 15h04

J'imagine que l'exemple n'est pas suffisamment représentatif et que Toto Tata peut intervenir sur le sites B puis sur Grenoble puis encore sur site B.
La méthode de Tabibitosan, par exemple, permet de regrouper les contact/site par tranche de date :

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
with data as (
select 'Toto Tata' as name, to_date ('25/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss') as DATERDV, 'Site B' as NOM from dual union all
select 'Toto Tata', to_date ('26/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('27/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('28/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('29/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('30/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('31/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('01/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('02/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Eric Toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Maillot toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Maillot toto', to_date ('04/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual
)
     , groupe_contact_site as (
select d.*
     ,   row_number() over(partition by name order by daterdv)
       - row_number() over(partition by name, nom order by daterdv) as grp
  from data d
 --order by name, daterdv
)
select name
     , min(daterdv) as date_debut
     , max(daterdv) as date_fin
     , nom
  from groupe_contact_site
 group by name, nom, grp
 order by name desc, date_debut;

Il suffit de remplacer les SELECT ... UNION ALL de la CTE data par la requête utilisée pour récupérer les données bruts.

**kekesar** · 20/07/2020, 16h30

Envoyé par vanagreg

Bonjour,

Quelle est la version de ta base de données?

S'il n'y a pas de "trous" entre les dates alors simplement:

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
SELECT
	concat(CT.NOM,concat( ' ', CT.Prenom)) AS NAME,   
	min(DATERDV) date_debut,
        max(dateredv) date_fin,     
	ST.NOM 
FROM
	planning pg 
		INNER JOIN CODIF CD 
		ON CD.ID_CODIF = PG.REF_TYPEEVEN 
			INNER JOIN CONTACT CT 
			ON CT.ID_CONTACT = PG.REF_CONCERNE 
				INNER JOIN SITE ST 
				ON ST.ID_SITE = PG.REF_SITE 
WHERE
	pg.REF_TYPEEVEN IN (3,
	25,
	30,
	31) AND
	DATERDV > CURRENT_DATE AND
	VALEUR LIKE '%FORMATION%' 
GROUP BY concat(CT.NOM,concat( ' ', CT.Prenom)),     
         ST.NOM 
ORDER BY 1, 2;

En Oracle 18c.

Et du coup ça fonctionne !
Par contre je veux bien quelque explication histoire d'apprendre de mes erreurs !

**kekesar** · 20/07/2020, 16h31

Envoyé par skuatamad

J'imagine que l'exemple n'est pas suffisamment représentatif et que Toto Tata peut intervenir sur le sites B puis sur Grenoble puis encore sur site B.
La méthode de Tabibitosan, par exemple, permet de regrouper les contact/site par tranche de date :

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
with data as (
select 'Toto Tata' as name, to_date ('25/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss') as DATERDV, 'Site B' as NOM from dual union all
select 'Toto Tata', to_date ('26/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('27/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('28/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('29/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('30/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('31/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('01/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('02/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Eric Toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Maillot toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Maillot toto', to_date ('04/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual
)
     , groupe_contact_site as (
select d.*
     ,   row_number() over(partition by name order by daterdv)
       - row_number() over(partition by name, nom order by daterdv) as grp
  from data d
 --order by name, daterdv
)
select name
     , min(daterdv) as date_debut
     , max(daterdv) as date_fin
     , nom
  from groupe_contact_site
 group by name, nom, grp
 order by name desc, date_debut;

Il suffit de remplacer les SELECT ... UNION ALL de la CTE data par la requête utilisée pour récupérer les données bruts.

C'est exactement ça, ça depend du formateur et des rendez-vous qu'il à.
Je vais essayer, et pourrais tu m'expliquer juste un peu plus car même avec la doc j'ai du mal à comprendre ce qui est fais exactement

**skuatamad** · 20/07/2020, 17h52

Il faut décomposer les étapes pour voir ce que fait la requête.
L'objectif est d'identifier par contact les ruptures de site :
https://www.developpez.net/forums/d1...e-tabibitosan/

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
 
SQL>   with data as (
select 'Toto Tata' as name, to_date ('25/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss') as DATERDV, 'Site B' as NOM from dual union all
select 'Toto Tata', to_date ('26/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('27/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('28/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('29/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('30/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Toto Tata', to_date ('31/08/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('01/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Toto Tata', to_date ('02/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Eric Toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'GRENOBLE' from dual union all
select 'Maillot toto', to_date ('03/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual union all
select 'Maillot toto', to_date ('04/09/2020 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'Site B' from dual
)
     , groupe_contact_site as (
select d.*
     , row_number() over(partition by name order by daterdv) as rn_name
         , row_number() over(partition by name, nom order by daterdv) as rn_nom
     ,   row_number() over(partition by name order by daterdv)
       - row_number() over(partition by name, nom order by daterdv) as grp
  from data d
 --order by name, daterdv
)
select *
  from groupe_contact_site
 order by name, daterdv;
 
NAME         DATERDV   NOM         RN_NAME     RN_NOM        GRP
------------ --------- -------- ---------- ---------- ----------
Eric Toto    03-SEP-20 GRENOBLE          1          1          0
Maillot toto 03-SEP-20 Site B            1          1          0
Maillot toto 04-SEP-20 Site B            2          2          0
Toto Tata    25-AUG-20 Site B            1          1          0
Toto Tata    26-AUG-20 Site B            2          2          0
Toto Tata    27-AUG-20 Site B            3          3          0
Toto Tata    28-AUG-20 Site B            4          4          0
Toto Tata    29-AUG-20 GRENOBLE          5          1          4
Toto Tata    30-AUG-20 GRENOBLE          6          2          4
Toto Tata    31-AUG-20 Site B            7          5          2
Toto Tata    01-SEP-20 Site B            8          6          2
Toto Tata    02-SEP-20 Site B            9          7          2
 
12 rows selected.
 
SQL>

Vous pouvez également utiliser la clause MODEL idéal pour ce genre de besoin, enfin surtout pour des besoins un peu plus poussés.

Manipulation de données sur des dates

SQL Oracle

Discussions similaires

Partager

Partager