Accueil du site > Les articles > Identifier une rupture de numéros de séquences en SQL
Version à imprimer Enregistrer au format PDF

Identifier une rupture de numéros de séquences en SQL

mardi 3 juillet 2007, par David Malle Visites  2381

Des enregistrements pour un regroupement donné comporte un numéro de séquence. Comment identifier les enregistrements qui ont une rupture dans ce numéro de séquence ? C’est possible grâce à une requête SQL, pas si compliquée que cela.


Exemple utilisé

Une table TABLEA comporte les zones :

- ANNEE
- MOIS
- CODSOC
- CODMAT
- ZONED
- ZONEE
- ZONEF

La table stocke pour chaque année / mois, les codes sociétés, matricules et infos associées.

Problème posé

En théorie, pour une année, il ne doit pas y avoir de rupture dans le numéro de mois. un couple société / matricule ne peut pas être présent en août 2006 et en octobre 2006 mais pas en septembre 2006.

Le fichier est énorme. Comment rapidement identifier les cas anormaux pour une année donnée ?

Contruisons une requête qui pour une clé donnée (ici code société + code matricule) compare : la valeur maximum du numéro de séquence - la valeur minimum du numéro de séquence + 1 et le nombre d’éléments trouvés pour la clé.

Par exemple, pour un code société AAA et un matricule M01, si l’on a les enregistrements suivants :

- 2006, 03, AAA, M01, ZONE1, ZONE2
- 2006, 04, AAA, M01, ZONE1, ZONE2
- 2006, 05, AAA, M01, ZONE1, ZONE2

On ne détectera pas de rupture car max(mois) = 5, min(mois) = 3 et le nombre d’occurence = 3 donc, 5 - 3 + 1 = 3.

Ce couple société + matricule n’est donc pas sélectionné.

Par exemple, pour un code société AAA et un matricule M01, si l’on a les enregistrements suivants :

- 2006, 03, AAA, M01, ZONE1, ZONE2
- 2006, 05, AAA, M01, ZONE1, ZONE2

On détectera une rupture car max(mois) = 5, min(mois) = 3 et le nombre d’occurence = 2 donc, 5 - 3 + 1 <> 2.

Ce couple société + matricule est donc sélectionné.

Une fois les couples société + matricules sélectionnés, une jointure est faîte sur le même fichier, pour récupérer les valeurs d’informations associées.

La requête SQL pour une année donnée

WITH TMP AS (
SELECT
CODSOC , CODMAT, COUNT(*) , MIN(MOIS), MAX(MOIS)
FROM
TABLEA
WHERE ANNEE = 2006
GROUP BY CODSOC, CODMAT
HAVING MAX(MOIS) - MIN(MOIS) + 1 <> COUNT(*)
)
SELECT B.ANNEE, B.MOIS, A.CODSOC, A.CODMAT, B.ZONE1, B.ZONE2
FROM TMP A
INNER JOIN TABLEA B
 ON B.CODSOC = A.CODSOC
AND B.CODMAT = A.CODMAT
ORDER BY A.CODSOC, A.CODMAT, B.MOIS

La requête SQL sur tout le fichier, quelque soit l’année

Si on veut lancer cette requête sur l’ensemble du fichier, quelque soit l’année, il faut mettre en place une formule de calcul pour établir une séquence entre les mois (passage du mois 12 d’une année au mois 1 de l’année suivante à gérer).

- On va faire cela en établissant l’année miminimum existant dans le fichier. Considérons 1995 dans notre cas.

Cela nous donne la requête suivante pour avoir la liste des codes sociétés + matricules ayant une rupture :

SELECT
CODSOC , CODMAT, COUNT(*) AS NOMBRE, MAX((ANNEE-1995)*12+MOIS)-MIN((ANNEE-1995)*12+MOIS)+1 AS AMPLITUDE
FROM
TABLEA
GROUP BY CODSOC, CODMAT
HAVING MAX((ANNEE-1995)*12+MOIS)- MIN((ANNEE-1995)*12+MOIS)+1 <> COUNT(*)

NOMBRE est le nombre de fois ou le matricule est trouvé. AMPLITUDE est le nombre de mois ou le matricule devrait être trouvé (différence entre l’année/mois le plus grand et le plus petit).

S’il n’y a pas rupture pour un matricule, NOMBRE = AMPLITUDE.