Trouver l'élément commun

Bonjour,
Je bloque à la résolution de mon problème, pour info il s'agit d'une base MS SQL 2005.
J'ai une liste de salariés avec par ordre la société, le matricule, la date de début, le type de sortie, la date de fin et le motif de sortie. Pour une meilleure compréhension j'ai généré un exemple ci dessous :

WITH TMP3 AS ( SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,36876 AS E_S_DATE, 2 AS E_S_TYP, 39256 AS E_S_DATES,'CP' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39257 AS E_S_DATE, 3 AS E_S_TYP, 39478 AS E_S_DATES,'CP' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39479 AS E_S_DATE, 2 AS E_S_TYP, 39683 AS E_S_DATES,'CP' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39684 AS E_S_DATE, 3 AS E_S_TYP, 39700 AS E_S_DATES,'CP' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39701 AS E_S_DATE, 1 AS E_S_TYP, 39750 AS E_S_DATES,'DEM' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39850 AS E_S_DATE, 2 AS E_S_TYP, 39900 AS E_S_DATES,'CP' AS MES_IDS
UNION
SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39901 AS E_S_DATE, 2 AS E_S_TYP, 42367 AS E_S_DATES,'CP' AS MES_IDS )

SELECT ENT_ID,SAL_MATR,E_S_DATE,E_S_TYP,E_S_DATES,MES_IDS
FROM TMP3

cela ressemble au final à cela :
ENT_ID SAL_MATR E_S_DATE E_S_TYP E_S_DATES MES_IDS
KAR 20012 36876 2 39256 CP
KAR 20012 39257 3 39478 CP
KAR 20012 39479 2 39683 CP
KAR 20012 39684 3 39700 CP
KAR 20012 39701 1 39750 DEM
KAR 20012 39850 2 39900 CP
KAR 20012 39901 2 42367 CP

Je veux pouvoir grouper ces données de façon à n'avoir plus qu'une seule ligne par période juxtaposée. Ici on a deux périodes, la première du 36876 au 39750 puis du 39850 au 42367, je souhaite donc n'avoir plus que deux lignes avec ces dates (les dates sont gérées en INT afin de simplifier le système, il n'y a pas besoin des heures).

le résultat doit ressembler à cela
ENT_ID SAL_MATR E_S_DATE E_S_TYP E_S_DATES MES_IDS
KAR 20012 36876 1 39750 DEM
KAR 20012 39850 2 42367 CP

Mes connaissances en sql ne me semblent pas suffisantes pour résoudre ce problème. Selon moi il y a deux solutions :
- soit on trouve comment indiquer un élément commun aux enregistrements d'une même période puis on fait un group by avec un min et un max (je ne trouve pas comment faire)
- soit on vérifie pour chaque enregistrement s'il y en a un qui est juste avant et accolé (date de début = date de fin -1 de l'enregistrement précédent) mais il faudrait faire cela en boucle deux par deux en groupant à chaque fois chacune des deux lignes (je ne sais pas comment faire cela)

Si quelqu'un peut m'aider ou bien a une autre idée, vous êtes le bienvenue.
Merci par avance

Bonsoir,

La solution est très simple en passant par un curseur sur la table ordonnée par E_S_DATE.
Si tu as bsoin de plus de détail, juste donne le résultat escompté et je t'aide dans le code.

Je n'ai pas appris l'utilisation des curseurs malheureusement, j'étais plutôt là en train de chercher du coté des requêtes récursives.

le résultat pour le lot de données ci-dessus doit être :
ENT_ID SAL_MATR E_S_DATE E_S_TYP E_S_DATES MES_IDS
KAR 20012 36876 1 39750 DEM
KAR 20012 39850 2 42367 CP

les colonnes E_S_TYP et MES_IDS doivent prendre les valeurs du dernier enregistrement du groupe mais là n'est pas le souci, je peux me débrouiller pour ces colonnes là.

Cela fait partie des requêtes les plus complexes qui existent. Date et Stonegrass ont passé des années à rechercher comment les résoudre de manière élégante. La derniere fois que j'ai du faire une telle requête j'ai facturé cela deux jours de travail.

En fait il s'agit de réaliser un agrégat temporel qui réalise une union des périodes recouvrantes.

Effectivement si votre volume de données est faible, alors vous pouvez utiliser un curseur. Sinon, optez pour la requête.
dans ce dernier cas deux solutions :
1) amusez vous quelques semaines dessus
2) trouvez un prestataire comme moi qui soit capable de vous la faire.

A +

J'espérais une autre réponse de votre part Mr Brouard mais j'avoue que vous m'avez déjà bien aidé, cela fait un an que j'ai commencé à me pencher sur le TSQL et vos articles tout comme votre livre que j'ai acheté m'ont bien servi.

1ère idée :
En effet il semble bien que les curseurs soient une solution puisqu'il agissent ligne par ligne et que dans le cas présent il s'agirait de classer par ordre chronologie puis de vérifier la ligne précédente afin de pouvoir déterminer un élément commun et ainsi pouvoir agréger les lignes ensemble. Cependant je ne sais pas utiliser les curseurs mais j'apprendrai quand j'aurai du temps.

2ème idée :
Utiliser la récursivité avec les CTE, mais là aussi je ne sais pas comment faire, en gros cela reviendrait à faire la chose suivante mais en boucle jusqu'à ce qu'on ait identifié toutes les lignes :
WITH TMP1 AS (select 'KAR' as Ste, 20012 as Mat, 39040 as Du,39100 as Au
union select 'KAR' as Ste, 20012 as Mat, 39101 as Du,39130 as Au
union select 'KAR' as Ste, 20012 as Mat, 39131 as Du,39150 as Au
union select 'KAR' as Ste, 20012 as Mat, 39151 as Du,39167 as Au
union select 'KAR' as Ste, 20012 as Mat, 39190 as Du,39203 as Au
union select 'KAR' as Ste, 20012 as Mat, 39204 as Du,39223 as Au ),
TMP2 as ( select Ste,Mat,Du,Au ,case when exists(select * from TMP1 as TAB2 where TAB1.Ste=TAB2.Ste and TAB1.Mat=TAB2.Mat and TAB1.Du=TAB2.Au+1 ) then NULL else Du end as Debut FROM TMP1 as TAB1),
TMP3 as ( select Ste,Mat,Du,Au ,case when Debut is null then (select Debut from TMP2 as TAB2 where TAB1.Ste=TAB2.Ste and TAB1.Mat=TAB2.Mat and AB1.Du=TAB2.Au+1 ) else Debut end as Debut FROM TMP2 as TAB1),
TMP4 as ( select Ste,Mat,Du,Au ,case when Debut is null then (select Debut from TMP3 as TAB2 where TAB1.Ste=TAB2.Ste and TAB1.Mat=TAB2.Mat and TAB1.Du=TAB2.Au+1 ) else Debut end as Debut FROM TMP3 as TAB1),
TMP5 as ( select Ste,Mat,Du,Au ,case when Debut is null then (select Debut from TMP4 as TAB2 where TAB1.Ste=TAB2.Ste and TAB1.Mat=TAB2.Mat and TAB1.Du=TAB2.Au+1 ) else Debut end as Debut FROM TMP4 as TAB1)
select * from TMP5

3ème idée et ma solution :
D'abord un exemple :
WITH TMP3 AS ( SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,36876 AS E_S_DATE, 2 AS E_S_TYP, 39256 AS E_S_DATES,'CP' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39257 AS E_S_DATE, 3 AS E_S_TYP, 39478 AS E_S_DATES,'CP' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39479 AS E_S_DATE, 2 AS E_S_TYP, 39683 AS E_S_DATES,'CP' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39684 AS E_S_DATE, 3 AS E_S_TYP, 39700 AS E_S_DATES,'CP' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39701 AS E_S_DATE, 1 AS E_S_TYP, 39750 AS E_S_DATES,'DEM' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39765 AS E_S_DATE, 1 AS E_S_TYP, 39810 AS E_S_DATES,'DEM' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39850 AS E_S_DATE, 2 AS E_S_TYP, 39900 AS E_S_DATES,'CP' AS MES_IDS
UNION SELECT 'KAR' AS ENT_ID,'20012' AS SAL_MATR,39901 AS E_S_DATE, 3 AS E_S_TYP, 42367 AS E_S_DATES,'CP' AS MES_IDS ),

TMP4 AS ( SELECT ENT_ID,SAL_MATR,E_S_DATE,E_S_TYP,E_S_DATES,MES_IDS
,CASE WHEN NOT EXISTS (SELECT * FROM TMP3 AS TAB2 WHERE TAB2.ENT_ID=TAB1.ENT_ID AND TAB2.SAL_MATR=TAB1.SAL_MATR AND TAB2.E_S_DATES+1=TAB1.E_S_DATE) THEN E_S_DATE ELSE NULL END AS Debut
,CASE WHEN NOT EXISTS (SELECT * FROM TMP3 AS TAB2 WHERE TAB2.ENT_ID=TAB1.ENT_ID AND TAB2.SAL_MATR=TAB1.SAL_MATR AND TAB2.E_S_DATE-1=TAB1.E_S_DATES) THEN E_S_DATES ELSE NULL END AS Fin
FROM TMP3 AS TAB1 )

SELECT ENT_ID,SAL_MATR,E_S_DATE,E_S_TYP,E_S_DATES,MES_IDS
,CASE WHEN Debut IS NULL THEN (SELECT MAX(Debut) FROM TMP4 AS TAB2 WHERE Debut IS NOT NULL AND TAB2.ENT_ID=TAB1.ENT_ID AND TAB2.SAL_MATR=TAB1.SAL_MATR AND TAB2.Debut<TAB1.Fin GROUP BY ENT_ID,SAL_MATR) ELSE Debut END AS Debut , Fin
FROM TMP4 AS TAB1 WHERE Fin IS NOT NULL

L'idée est la suivante. Je n'ai finalement pas besoin des lignes intermédiaires qui se trouvent à l'intérieur de la période, donc je vais marquer uniquement la première et la dernière ligne du sous ensemble avec l'élément connu, c'est à dire pour la première ligne la date de début et pour la dernière ligne la date de fin. Puis dans une requête suivante je ne retiens que les dernières lignes auxquelles je vais ajouter s'il n'existe pas la valeur du début (s'il n'y a qu'une ligne le début et la fin sont connus de suite), celle ci correspondant à la valeur maximum du Début pour les lignes qui ont une date de début inférieur à la date de fin de ma ligne actuelle.
Ce n'est peut être pas très clair mais en décortiquant le code vous devriez comprendre.

Si vous quelqu'un a une autre idée, je suis preneur.

Bonjour,

J'ai plusieurs solutions pour ton problème.
Une dernière question pour t'envoyer ma réponse :
est ce que les deux champs E_S_DATE et E_S_DATES sont uniques ou risquent d'avoir des doublons. S'ils sont uniques, j'ai trois solutions. Sinon, j'en ai deux. Dans tous les cas, tu apprendras avec les curseurs ;)

Bonjour,
E_S_DATE correspond à la date d'entrée et E_S_DATES à la date de sortie. Dans l'exemple il n'y a qu'un matricule (SAL_MATR) mais dans les faits il y en a évidemment plusieurs et un matricule peut avoir plusieurs sociétés (ENT_ID) mais il s'agit de la même personne quand même. Dans cette première requête le regroupement se fait donc par Société et Matricule, et une date d'entrée comme de sortie ne peut apparaitre qu'une seul fois avec un même Matricule.
Cependant il peut y a voir des doublons sur les dates mais celles ci font référence à des Matricules différents.
Le problème avec les curseurs si j'ai bien compris c'est qu'ils prennent beaucoup de ressource et de mémoire donc sur de grandes tables cela semble exclu. De plus ici je crée une vue et il semble qu'on ne puisse pas utiliser des curseurs dans une vue mais seulement dans une procédure stockée.
Cependant vous avez raison je suis certain qu'il y a là avec les curseurs des choses intéressantes à apprendre, tout est affaire de temps comme d'habitude.
Si vous avez le code avec les curseurs je suis preneur mais ne vous embêtez pas trop non plus j'ai trouvé là une solution qui marche très bien, vous ne m'avez d'ailleurs pas dit ce que vous en pensez.
Merci pour votre aide

Bonjour,

En ce qui concerne ta solution, elle marche surtout que vous l'avez testée. Seulement vous avez un code ressemblant à :

Code:

---

select champ1, ( select schamp1 from TABLE2 ) as champ2 from TABLE1

---

Sur une sélection pareille, pour chaque ligne de la table TABLE1, il y aura exécution du select sur la table TABLE2. Cela dit, nous aurons autant de select que de ligne sur la TABLE1 et cela donnera des temps de réponses très lents selon la taille des tables. J'espère que vous voyez cz que je veux dire.

Pour les curseurs, on peut les utiliser aussi sur des fonctions. De là, ma solution serait de créer une fonction qui utilise un curseur pour retourner une table avec juste les bornes finales. Puis ramener pour chaque borne les données correspondantes sous condition que les champs de retour de la fonction soient determinants pour récupérer les autres champs.

J'espèe que j'étais claire dans mon explication. Nous sommes tous là pour nous entre-aider. Cela est un plaisir pour tout développeur. :)
Bonne chance.