Trouver les dates manquantes entre 2 dates ?

**Zebulon777** · 14/06/2019, 10h17

Bonjour à tous.

J'ai besoin d'un petit (gros ?) coup de main

Je dois lister toutes les dates manquantes entre 2 dates.

On considère une table toute bête : date - nb_arr - nb_dep.
Il devrait y avoir un enregistrement par jour.
Dans le cadre de nos vérifications automatiques, il faut donc que je liste les dates pour lesquelles y il a eut un souci.

Merci d'avance.

**tatayo** · 14/06/2019, 11h06

Bonjour,
Pour moi le plus simple et le plus efficace est d'avoir une table calendrier avec toutes les dates.
Ainsi la recherche sera une simple requête sur cette table avec un NON EXISTS par exemple.

Tatayo.

**Zebulon777** · 14/06/2019, 11h14

D'abord merci de te pencher sur mon problème.

Le problème de la table calendrier, c'est qu'il faut qu'elle soit remplie et toujours à jour ? Ou alors, est-ce qu'il y a un moyen de faire autrement pour remplir la table calendrier ?
(D'autant plus que nous allons avoir des données qui vont remonter jusqu'en 2000 !)

Il n'y aurait pas moyen de faire autrement ?

**tatayo** · 14/06/2019, 11h26

Il suffit de remplir la date une bonne fois pour toute. Une ligne par jour, ça ne fait que 36500 lignes* pour 100 ans, de quoi voir venir !

Tatayo.

* Ok, un peu plus pour les années bissextiles, mais on ne va pas chipoter

**Zebulon777** · 14/06/2019, 13h40

Moui...

Par contre, en SQL, je ne vois pas comment faire ?
(A noter que je ne sais que utiliser PhpMyAdmin pour exécuter le SQL)

**Zebulon777** · 14/06/2019, 14h03

J'ai trouvé ça sur le net :

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
 
CREATE TABLE `T` (
  `n` int(11)
);
 
 
insert into T (n) values (1),(2),(3),(4),(5),(6),(7);
 
-- time span
SET @d0 = "2012-01-01";
SET @d1 = "2012-01-07";
 
SET @date = date_sub(@d0, interval 1 day);
 
-- set up the time dimension table
DROP TABLE IF EXISTS time_dimension;
CREATE TABLE `time_dimension` (
  `date` date 	 DEFAULT NULL,
  `id` 	 int 	 NOT NULL,
  `y` 	 smallint DEFAULT NULL,
  `m` 	 smallint DEFAULT NULL,
  `d` 	 smallint DEFAULT NULL,
  `yw` 	 smallint DEFAULT NULL,
  `w` 	 smallint DEFAULT NULL,
  `q` 	 smallint DEFAULT NULL,
  `wd` 	 smallint DEFAULT NULL,
  `m_name`  char(10) DEFAULT NULL,
  `wd_name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
-- populate the table with dates
INSERT INTO time_dimension
SELECT @date := date_add(@date, interval 1 day) as date,
    -- integer ID that allows immediate understanding
    date_format(@date, "%Y%m%d") as id,
    year(@date) as y,
    month(@date) as m,
    day(@date) as d,
    date_format(@date, "%x") as yw,
    week(@date, 3) as w,
    quarter(@date) as q,
    weekday(@date)+1 as wd,
    monthname(@date) as m_name,
    dayname(@date) as wd_name
FROM T
WHERE date_add(@date, interval 1 day) <= @d1
ORDER BY date

Ca fonctionne bien, mais j'ai 2 questions :
- je ne comprends pas à quoi sert la table T ?
- et surtout est-ce qu'il y a moyen de remplacer le remplissage de la table T par une boucle pour aller du 01/01/2015 au 31/12/2050 (par exemple), le tout en SQL ?
~~- ha, et aussi comment avoir un remplissage avec les nom français ?~~ J'ai trouvé, il faut ajouter SET lc_time_names = 'fr_FR';

**tatayo** · 14/06/2019, 14h58

C'est simple: pour trouver les dates manquantes dans une table pour une période donnée, tu peux utiliser une requête de ce genre:

Code sql :

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

1
2
3
4
5
 
Select td.*
from TableDate as td
where not exist (select 1 from LaTableCible as ltc where lcd.ColDate = td.ColDate)
and td.ColDate between '2019-01-01' and '2019-12-31'

Avec les indexes qui vont bien (un unique sur TableDate.ColDate par exemple), ce genre de requête devrait tourner sans problème.

Tatayo.

**Zebulon777** · 14/06/2019, 15h15

Ouah, super.
LaTableCible est la table dans laquelle je veux effectuer la recherche, si j'ai bien compris, mais c'est quoi TableDate ? La table "calendrier" dont tu me parlais ?

**tatayo** · 14/06/2019, 15h18

Tout à fait.
Pour le remplissage, peu importe la méthode, vu que tu le fais une bonne fois pour toute.

Tatayo.

**Zebulon777** · 14/06/2019, 15h41

Oui, c'est pas faux...

Regarde, j'ai trouvé aussi ça :

Code sql :

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
 
create table DIM_DATE
(
   ID_DATE        NUMBER(8)            not null,
   DATE_DU_JOUR         DATE                 not null,
   ANNEE_CALENDAIRE     NUMBER(4),
   SEMESTRE             NUMBER(1),
   LIBELLE_SEMESTRE     VARCHAR2(250 CHAR),
   TRIMESTRE            NUMBER(1),
   LIBELLE_TRIMESTRE    VARCHAR2(250 CHAR),
   ANNEE_MOIS           NUMBER(6),
   MOIS                 NUMBER(2),
   LIBELLE_MOIS         VARCHAR2(250 CHAR),
   SEMAINE              NUMBER(2),
   JOUR                 NUMBER(2),
   LIBELLE_JOUR         VARCHAR2(250 CHAR),
   JOUR_FERIE           CHAR(3 CHAR),
   JOUR_OUVRE           CHAR(3 CHAR),
   QUANTIEME_JOUR       NUMBER
);
 
INSERT INTO DIM_DATE (ID_DATE, DATE_DU_JOUR, ANNEE_CALENDAIRE, SEMESTRE, LIBELLE_SEMESTRE, TRIMESTRE, LIBELLE_TRIMESTRE, ANNEE_MOIS,MOIS, LIBELLE_MOIS, SEMAINE, JOUR, LIBELLE_JOUR, JOUR_FERIE, JOUR_OUVRE, QUANTIEME_JOUR)
SELECT
    TO_NUMBER(TO_CHAR(DT_CAL, 'YYYYMMDD')) AS ID_CALENDRIER,
    DT_CAL AS DATE_DU_JOUR,
    TO_NUMBER(TO_CHAR(DT_CAL, 'YYYY')) AS ANNEE_CALENDAIRE,
    ROUND(TO_NUMBER(TO_CHAR(DT_CAL, 'Q'))/2) AS SEMESTRE,
    CASE ROUND(TO_NUMBER(TO_CHAR(DT_CAL, 'Q'))/2) WHEN 1 THEN '1er semestre' ELSE '2ème semestre' END AS LIBELLE_SEMESTRE,
    TO_NUMBER(TO_CHAR(DT_CAL, 'Q')) AS TRIMESTRE,
    CASE TO_NUMBER(TO_CHAR(DT_CAL, 'Q')) WHEN 1 THEN '1er trimestre' ELSE TO_NUMBER(TO_CHAR(DT_CAL, 'Q')) || 'ème trimestre' END AS LIBELLE_TRIMESTRE,
    TO_NUMBER(TO_NUMBER(TO_CHAR(DT_CAL, 'YYYY')) || LPAD(TO_CHAR(DT_CAL, 'MM'), 2, '0')) AS ANNEE_MOIS,
    TO_NUMBER(TO_CHAR(DT_CAL, 'MM')) AS MOIS,
    TO_CHAR(DT_CAL, 'Month') AS LIBELLE_MOIS,
    TO_NUMBER(TO_CHAR(DT_CAL, 'IW')) AS SEMAINE,
    TO_NUMBER(TO_CHAR(DT_CAL, 'DD')) AS JOUR,
    TO_CHAR(DT_CAL, 'Day') AS LIBELLE_JOUR,
    CASE WHEN TO_CHAR(DT_CAL, 'D') IN ('6', '7') THEN 'Oui' ELSE 'Non' END AS JOUR_FERIE,
    CASE WHEN TO_CHAR(DT_CAL, 'D') IN ('6', '7') THEN 'Non' ELSE 'Oui' END AS JOUR_OUVRE,
    NUM_JOUR AS QUANTIEME_JOUR
FROM
(
    SELECT to_date('19000101','YYYYMMDD') + (rownum - 1) AS DT_CAL, rownum AS NUM_JOUR
    FROM dual
    connect BY to_date('19000101','YYYYMMDD') + (rownum - 1) <= to_date('29991231','YYYYMMDD')
);
 
COMMIT;

Ca aurait été encore mieux, mais ça ne fonctionne pas sous MySql avec PhpMyAdmin...

Tu crois que c'est adaptable ?

**tatayo** · 14/06/2019, 15h58

Très honnêtement je ne sais pas, je n'utilises pas MySQL.
Mais une simple boucle en PHP/C#/WinDev/LeLangageQueTuMaitrises fera très bien l'affaire.

Tatayo.

**Zebulon777** · 17/06/2019, 08h12

En tout cas, merci de ton aide.

**escartefigue** · 19/06/2019, 08h14

Le plus simple pour créer une table calendrier est d'utiliser une requête récursive.
Pour cela, il faut pouvoir utiliser les CTE, c'est à dire disposer de la version MySQL 8.0 ou MariaDB 10.2

**Zebulon777** · 19/06/2019, 08h16

Merci, mais, il va falloir m'en dire plus, je ne connais pas ?

Pour le développement, j'utilise Wamp et donc je suis en MySql 5.7.21 et en MariaDB 10.2.14, mais je n'utilise que MySQL.

**escartefigue** · 19/06/2019, 11h30

Voici un exemple fait sous DB2 faute de Mysql sur mon poste

Création d'une CTE que j'ai appelée TAB0 pour les libellés des jours de la semaine (du lundi au dimanche) et d'une autre CTE appelée TAB1 qui utilise une requête récursive afin de créer le calendrier que j'ai limité au 20 premiers jours dans cet exemple

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 tab0(Tjsm, Tlbj) as                                  
       (select 1 as c1, 'LUN' as c2   from sysibm.sysdummy1 
        union all                                            
        select 2 as c1, 'MAR' as c2   from sysibm.sysdummy1  
        union all                                            
        select 3 as c1, 'MER' as c2   from sysibm.sysdummy1  
        union all                                            
        select 4 as c1, 'JEU' as c2   from sysibm.sysdummy1  
        union all                                            
        select 5 as c1, 'VEN' as c2   from sysibm.sysdummy1  
        union all                                            
        select 6 as c1, 'SAM' as c2   from sysibm.sysdummy1  
        union all                                            
        select 7 as c1, 'DIM' as c2   from sysibm.sysdummy1  
       )                                                     
     , tab1(Tseq, Tdte, Tjsm, Tlbj, Tsem) as       
      (select cast(001 as integer)                 
            , cast('2016-01-01' as date)           
            , dayofweek_iso('2016-01-01')          
            , T2.Tlbj                              
            , week_iso('2016-01-01')               
       from sysibm.sysdummy1 T1                    
       inner join Tab0 T2                          
         on t2.Tjsm = dayofweek_iso('2016-01-01')  
       union all                                   
       select T1.Tseq+1                            
            , T1.Tdte+1 days                       
            , dayofweek_iso(T1.Tdte+1 days)        
            , T2.Tlbj                              
            , week_iso(T1.Tdte+1 days)             
       from tab1 T1                                
       inner join Tab0 T2                          
         on t2.Tjsm = dayofweek_iso(T1.Tdte+1 days)
       where T1.Tseq < 20)

La requête select * from tab1 donne :

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
1	2016-01-01	5	VEN	53
2	2016-01-02	6	SAM	53
3	2016-01-03	7	DIM	53
4	2016-01-04	1	LUN	1
5	2016-01-05	2	MAR	1
6	2016-01-06	3	MER	1
7	2016-01-07	4	JEU	1
8	2016-01-08	5	VEN	1
9	2016-01-09	6	SAM	1
10	2016-01-10	7	DIM	1
11	2016-01-11	1	LUN	2
12	2016-01-12	2	MAR	2
13	2016-01-13	3	MER	2
14	2016-01-14	4	JEU	2
15	2016-01-15	5	VEN	2
16	2016-01-16	6	SAM	2
17	2016-01-17	7	DIM	2
18	2016-01-18	1	LUN	3
19	2016-01-19	2	MAR	3
20	2016-01-20	3	MER	3

A adapter à la sauce MySQL V8 : avec MySQL, le premier jour de la semaine est le dimanche, l'instruction pour incrémenter une date est date_add(), le numéro de semaine se récupère via week() et bien sur, la pseudo-table "sysibm.sysdummy1" spécifique à DB2 n'est pas requise

**Zebulon777** · 19/06/2019, 12h52

Merci beaucoup.

**Zebulon777** · 19/06/2019, 13h23

Envoyé par tatayo

C'est simple: pour trouver les dates manquantes dans une table pour une période donnée, tu peux utiliser une requête de ce genre:

Code sql :

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

1
2
3
4
5
 
Select td.*
from TableDate as td
where not exist (select 1 from LaTableCible as ltc where lcd.ColDate = td.ColDate)
and td.ColDate between '2019-01-01' and '2019-12-31'

Avec les indexes qui vont bien (un unique sur TableDate.ColDate par exemple), ce genre de requête devrait tourner sans problème.

Tatayo.

Je relance, car j'ai enfin eu le temps de tester la requête et elle ne fonctionne pas.

Code sql :

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

1
2
3
4
5
 
Select CALENDRIER.*
from CALENDRIER as CL
where not exist (select 1 from heurglis_lfpo as hg where hg.HG_DATE = CL.date)
and CL.date between '2017-01-01' and '2017-12-31'

Cette requête me renvoie une erreur "MySQL a répondu:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 1 from heurglis_lfpo as hg where hg.HG_DATE = CL.date)
and CL.date betwe' at line 3"

Je cherche de mon côté, mais pour le moment, je ne trouve rien.

**escartefigue** · 19/06/2019, 13h26

la syntaxe est WHERE NOT EXISTS avec un "S" à la fin donc

**Zebulon777** · 19/06/2019, 13h48

Heu, et c'est là que je dis : "Oups"

Merci beaucoup encore une fois.

Par contre, pour info, pour ceux qui ne le saurai pas : MySql n'aime pas qu'on préfixe l'*, donc ça donne :

Code sql :

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

1
2
3
4
5
 
Select *
from CALENDRIER as CL
where not exists (select 1 from heurglis_lfpo as hg where hg.HG_DATE = CL.date)
and CL.date between '2017-01-01' and '2017-12-31'