Discussion :

[SQLpro]

Bonjour à tous.

Dans cet article :
Calculs SQL avec des dates : age exact révolu et anniversaires...
je propose deux requêtes (SQL Server, mais faciles à traduire dans les autres dialectes) permettant :

• de calculer l'age exact révolu en année des personnes
• de trouver les personnes dont l'anniversaire tombe entre deux dates données

Age exact révolu en années (fonctions SQL Server) :

Code :

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

1
2
3
4
5
6
7
SELECT *,
       DATEDIFF(YEAR, PRS_DATE_NAISSANCE, GETDATE())
       - CASE WHEN MONTH(PRS_DATE_NAISSANCE) < MONTH(GETDATE())  THEN -1
              WHEN MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE())
                   AND DAY(PRS_DATE_NAISSANCE) <= DAY(GETDATE()) THEN -1
              ELSE 0 END - 1 AS AGE
FROM   T_PERSONNE_PRS;

La même en pseudo SQL normatif :

Code :

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

1
2
3
4
5
6
7
8
SELECT *,
       EXTRACT(YEAR FROM CURRENT_DATE - PRS_DATE_NAISSANCE)
       - CASE WHEN EXTRACT(MONTH FROM PRS_DATE_NAISSANCE) < EXTRACT(MONTH FROM CURRENT_DATE)  THEN -1
              WHEN EXTRACT(MONTH FROM PRS_DATE_NAISSANCE) = EXTRACT(MONTH FROM CURRENT_DATE)
                   AND EXTRACT(DAY FROM PRS_DATE_NAISSANCE) <= EXTRACT(DAY FROM CURRENT_DATE) THEN -1
              ELSE 0
         END - 1 AS AGE
FROM   T_PERSONNE_PRS;

Anniversaire entre deux dates (fonctions MS SQL Server) :
-- avec dates du 20 décembre au 10 janvier (an + 1)

Code :

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

1
2
3
4
5
SELECT *
FROM   T_PERSONNE_PRS
WHERE          (MONTH(PRS_DATE_NAISSANCE) * 31 + DAY(PRS_DATE_NAISSANCE) ) % (31 * 12)
       BETWEEN (MONTH('2014-12-20')       * 31 + DAY('2014-12-20'))        % (31 * 12)
           AND (MONTH('2015-01-10')       * 31 + DAY('2015-01-10') + 372)  % (31 * 12);

La même en SQL normatif :

Code :

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

1
2
3
4
5
SELECT *
FROM   T_PERSONNE_PRS
WHERE          MOD((EXTRACT(MONTH FROM PRS_DATE_NAISSANCE) * 31 + EXTRACT(DAY FROM PRS_DATE_NAISSANCE) ) , (31 * 12))
       BETWEEN MOD((EXTRACT(MONTH FROM '2014-12-20')       * 31 + EXTRACT(DAY FROM '2014-12-20'))        , (31 * 12))
           AND MOD((EXTRACT(MONTH FROM '2015-01-10')       * 31 + EXTRACT(DAY FROM '2015-01-10') + 372)  , (31 * 12));

Mais je pense qu'il y a peut être des requêtes plus performantes...

Auriez vous quelques idées de rechange ?

Merci

[aieeeuuuuu]

Bonjour,

Pour l'âge exact, je verrais bien ceci, mais ça doit pas se jouer à grand chose (peut-être quelques calculs en moins...)

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT *,
       DATEDIFF(MONTH, PRS_DATE_NAISSANCE, GETDATE()) / 12
       - CASE 
			WHEN MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE()) 
			AND  DAY(PRS_DATE_NAISSANCE) > DAY(GETDATE()) 
				THEN 1 
			ELSE 0 
		END AS AGE
FROM   T_PERSONNE_PRS;

[SergioMaster]

Bonjour,

en ajoutant +1 à la date du jour , on n'aurait même pas besoin du CASE (a mon humble avis et sans l'avoir encore testé)
[Edit] au temps pour moi , en tout cas sous Firebird la proposition est fausse

[SQLpro]

Je viens de faire les métriques :

Code :

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

SET STATISTICS TIME ON;

Code :

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

1
2
3
4
5
6
7
SELECT *, 
       DATEDIFF(YEAR, PRS_DATE_NAISSANCE, GETDATE())
       - CASE WHEN MONTH(PRS_DATE_NAISSANCE) < MONTH(GETDATE())  THEN -1
              WHEN MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE()) 
                   AND DAY(PRS_DATE_NAISSANCE) <= DAY(GETDATE()) THEN -1
              ELSE 0 END - 1 AS AGE
FROM   T_PERSONNE_PRS;

-- Temps UC = 687*ms, temps écoulé = 8997*ms.
-- Temps UC = 577*ms, temps écoulé = 8839*ms.
-- Temps UC = 499*ms, temps écoulé = 9092*ms.
-- Temps UC = 406*ms, temps écoulé = 8962*ms.
-- Temps UC = 484*ms, temps écoulé = 9100*ms.
-- MOY : UC = 531 ms, temps écoulé = 8998

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *,
       DATEDIFF(MONTH, PRS_DATE_NAISSANCE, GETDATE()) / 12
       - CASE 
			WHEN MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE()) 
			AND  DAY(PRS_DATE_NAISSANCE) > DAY(GETDATE()) 
				THEN 1 
			ELSE 0 
		END AS AGE
FROM   T_PERSONNE_PRS;

-- Temps UC = 452*ms, temps écoulé = 8816*ms.
-- Temps UC = 546*ms, temps écoulé = 8662*ms.
-- Temps UC = 608*ms, temps écoulé = 8822*ms.
-- Temps UC = 546*ms, temps écoulé = 8554*ms.
-- Temps UC = 499*ms, temps écoulé = 8781*ms
-- MOY : UC = 530 ms, temps écoulé = 8727

Bref aucun gain significatif de calcul...

A +

[aieeeuuuuu]

Bref aucun gain significatif de calcul...

A +

Non, en effet, je pense de toute façon qu'il va être difficile d'obtenir un gain significatif sans passer par quelque artifice, comme des colonnes calculées persistantes (l'avantage, c'est que les dates de naissances changent rarement, la mienne en tout cas n'a jamais changé )

En revanche, la requête générique de calcul de date d'anniversaire approchante est incorrecte si la plage comprend le 1er décembre (en fait les calculs pour gérer le changement d'année ne font que déplacer le problème) :

Code :

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

1
2
3
4
5
6
 
SELECT *
FROM   T_PERSONNE_PRS
WHERE          (MONTH(PRS_DATE_NAISSANCE) * 31 + DAY(PRS_DATE_NAISSANCE) ) % (31 * 12)
       BETWEEN (MONTH('2014-11-20')       * 31 + DAY('2014-11-20'))        % (31 * 12)
           AND (MONTH('2014-12-10')       * 31 + DAY('2014-12-10') + 372)  % (31 * 12);

--> aucun résultat.

[SQLpro]

Oui, tu as raison, faut qu'on recale les 1 des mois et jours à 0...

Je pense que cette formulation est bonne :

Code :

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

1
2
3
4
5
6
 
SELECT *
FROM   T_PERSONNE_PRS
WHERE          ((MONTH(PRS_DATE_NAISSANCE) -1 ) * 31 + (DAY(PRS_DATE_NAISSANCE) -1 ) ) % (31 * 12)
       BETWEEN ((MONTH('2013-11-20') - 1 )      * 31 + (DAY('2013-11-20') - 1) )       % (31 * 12)
           AND ((MONTH('2013-12-10') - 1 )      * 31 + (DAY('2013-12-10') + 371) )     % (31 * 12);

A +

[aieeeuuuuu]

Non, c'est toujours faux.
Pas de résultat si la plage comprend le 1er janvier :

Code :

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

1
2
3
4
5
6
 
SELECT *
FROM   T_PERSONNE_PRS
WHERE          ((MONTH(PRS_DATE_NAISSANCE) -1 ) * 31 + (DAY(PRS_DATE_NAISSANCE) -1 ) ) % (31 * 12)
       BETWEEN ((MONTH('2013-12-20') - 1 )      * 31 + (DAY('2013-12-20') - 1) )       % (31 * 12)
           AND ((MONTH('2014-01-10') - 1 )      * 31 + (DAY('2014-01-10') + 371) )     % (31 * 12);

(D'ailleurs, il me semble que tu as remplacé la mauvaise requête dans l'article, celle du calcul d'âge au lieu de celle de la recherche d'anniversaire)
---


En fait je ne pense pas qu'il soit possible de traiter tous les cas avec un simple BETWEEN (à moins de le truffer de CASE WHEN), car le modulo ne fait que "déplacer" la rupture annuelle...

Personnellement, si je devais mettre en place ces deux types de requête, sachant qu'elle seraient appelées régulièrement, je ferais ceci :

Ajout de colonnes calculées et index :

Code :

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

1
2
3
4
5
6
7
8
 
 ALTER TABLE T_PERSONNE_PRS
	ADD	PRS_MOIS_JOUR_NAISSANCE AS (MONTH(PRS_DATE_NAISSANCE) * 100 + DAY(PRS_DATE_NAISSANCE) )  PERSISTED
	/* eventuellement : 	,PRS_ANNEE_NAISSANCE	AS (YEAR(PRS_DATE_NAISSANCE)) PERSISTED */
 GO 
 CREATE INDEX IX_PRS_MOIS_JOUR_NAISSANCE 
	ON T_PERSONNE_PRS(PRS_MOIS_JOUR_NAISSANCE) 
	INCLUDE(PRS_ID, PRS_DATE_NAISSANCE/*, PRS_ANNEE_NAISSANCE*/)

Puis j'écrirais la requête de calcul d'âge comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT	PRS_ID
	,PRS_DATE_NAISSANCE
	,
       YEAR(GETDATE()) 
       - YEAR(PRS_DATE_NAISSANCE)
       - CASE 
			WHEN MONTH(PRS_DATE_NAISSANCE)*100 + DAY(PRS_DATE_NAISSANCE) >  MONTH(GETDATE())*100+ DAY(GETDATE()) 
				THEN 1 
			ELSE 0 
		END AS AGE
FROM   T_PERSONNE_PRS

Et la requête de recherche d'anniversaire comme ceci :

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
 
DECLARE @Deb DATE = '2014-12-20'
DECLARE @Fin DATE = '2015-01-10'
 
SELECT	PRS_ID
	,PRS_DATE_NAISSANCE
FROM	T_PERSONNE_PRS 
WHERE
	( 
		YEAR(@Deb) = YEAR(@Fin) 
		AND
		MONTH(PRS_DATE_NAISSANCE)*100 + DAY(PRS_DATE_NAISSANCE)  
			BETWEEN 	MONTH(@Deb)*100 + DAY(@Deb) 
			AND		MONTH(@Fin)*100 + DAY(@Fin) 
	)
	OR
	( 
		YEAR(@Deb) < YEAR(@Fin) 
		AND
		MONTH(PRS_DATE_NAISSANCE)*100 + DAY(PRS_DATE_NAISSANCE) 
			NOT  BETWEEN 	MONTH(@Fin)*100 + DAY(@Fin) + 1 
			AND		MONTH(@Deb)*100 + DAY(@Deb) - 1
	)
OPTION (RECOMPILE)

-->

Temps UC = 16*ms, temps écoulé = 472*ms

En forçant la recompilation, ou deux requêtes distinctes (une pour chaque cas) au sein d'une SP sans le OPTION(RECOMPILE).