Discussion :

[Jamming Ed]

Bonjour à tous,

J'ai un petit problème sur un Update dont je n'arrive pas à me dépêtrer.

En gros, j'ai un fichier texte qui est importé dans une table. Chaque ligne de ce fichier comporte une référence de facture + une référence de moyen de paiement. Chaque référence de facture peut correspondre à plusieurs références de paiement (= 1 facture payée par M chèques) et une référence de moyen de paiement peut correspondre à plusieurs références de facture (= N factures payée par un chèque).

Après import dans la table je me retrouve donc avec quelque chose du style (simplifié pour plus de clarté) :

Code :

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

1
2
3
4
5
6
7
8
9
10
RefFact    RefMP   numDossier
-----------------------------
fact1        MP1       NULL
fact2        MP2       NULL
fact2        MP3       NULL
fact2        MP4       NULL
fact3        MP4       NULL
fact3        MP5       NULL
fact4        MP6       NULL
fact5        MP7       NULL

A partir de cela, je dois constituer des dossiers en fonction des documents qui vont ensemble.

Exemple de cas simples : {fact1, MP1} forment un dossier. Idem pour {fact4, MP6} et {fact5, MP7}

Exemple de cas plus difficile : fact2 est payé par MP2, MP3 et MP4. Il sont donc liés. Mais MP4 sert aussi à payer un bout de fact3, qui lui même... Au final, ce dossier est constitué de {fact2, fact3, MP2, MP3, MP4, MP5}

Je dois donc obtenir :

Code :

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

1
2
3
4
5
6
7
8
9
10
RefFact    RefMP   numDossier
-----------------------------
fact1        MP1       1
fact2        MP2       2
fact2        MP3       2
fact2        MP4       2
fact3        MP4       2
fact3        MP5       2
fact4        MP6       3
fact5        MP7       4

J'ai essayé différents bidouillages (que je ne montrerai pas, ils piquent tellement les yeux qu'on risque une épidémie de conjonctivite dans la communauté de dev SQL ).

J'ai notamment essayé une autojointure sur la ligne précédente (la table possède un id), mais ça ne fonctionne que si les lignes sont ordonnées dans le fichier et je viens d'apprendre que ça ne serait pas le cas. (Et de toute manière, j'ai appris ici que c'était une sorte d'hérésie...)

Quelqu'un a-t-il une solution pour ce problème ?

Merci d'avance !

PS : Pour info je suis sous SQL Server 2005

[Oishiiii]

J'ai essayé de faire quelque chose mais sans succès.

Je soupçonne l'utilisation de requête recursive et de fonction de fenêtrage pour résoudre le problème, mais ne maîtrisant pas je tourne en rond.

Avec un simple Count(*) et une sous-requête j'arrive quand même à récupérer les factures qui ont des références en commun mais je n'arrive pas à produire le résultat final:

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
WITH t(refFact, refMp) AS (
  select 'fact1', 'MP1'
  union
  select 'fact2', 'MP2'
  union
  select 'fact2', 'MP3'
  union
  select 'fact2', 'MP4'
  union
  select 'fact3', 'MP4'
  union
  select 'fact3', 'MP5'
  union
  select 'fact4', 'MP6'
  union
  select 'fact5', 'MP7'
),
a AS (
  SELECT Count(*) AS nbAct, refMp
  FROM t
  GROUP BY refMp
  HAVING Count(*) > 1
),
b AS (
  SELECT reffAct, refMp
  FROM t
  WHERE refMp IN (
    SELECT refMp from a
  )
)
SELECT * FROM b

Les pros du SQL trouveront certainement la solution

[Jamming Ed]

Merci pour le temps que tu y a passé même si pas concluant

Quelqu'un d'autre a une idée ?

[Waldar]

Pas facile en effet.
Voici une solution qui fonctionne mais n'est probablement pas très performante (génère beaucoup de lignes dans la partie récursive) et je suppose est améliorable.

J'ai inclu un Id dans le jeu de test :

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
WITH t (Id, RefFact, RefMp) AS
(
SELECT 1, 'fact1', 'MP1' union all
SELECT 2, 'fact2', 'MP2' union all
SELECT 3, 'fact2', 'MP3' union all
SELECT 4, 'fact2', 'MP4' union all
SELECT 5, 'fact3', 'MP4' union all
SELECT 6, 'fact3', 'MP5' union all
SELECT 7, 'fact4', 'MP6' union all
SELECT 8, 'fact5', 'MP7'
)
  ,  CTest AS
(
select Id as IdDeb, Id, RefFact, RefMp, 1 as Cpt,
       cast(Id as varchar(max)) as pth
  from t
 union all
select c.IdDeb, t.Id, t.RefFact, t.RefMp, c.Cpt + 1,
       c.pth + '-' + cast(t.Id as varchar(max))
  from t
       inner join CTest as c
         on c.RefFact = t.RefFact
         or c.RefMp   = t.RefMp
 where charindex(cast(t.Id as varchar(max)), c.pth) = 0
)
  select Id, RefFact, RefMp,
         dense_rank() over(order by min(IdDeb)asc) as numDossier
    from CTest
group by Id, RefFact, RefMp
order by Id asc;
 
Id          RefFact RefMp numDossier
----------- ------- ----- --------------------
1           fact1   MP1   1
2           fact2   MP2   2
3           fact2   MP3   2
4           fact2   MP4   2
5           fact3   MP4   2
6           fact3   MP5   2
7           fact4   MP6   3
8           fact5   MP7   4

En mélangeant un peu les données de départ :

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
WITH t (Id, RefFact, RefMp) AS
(
SELECT 1, 'fact1', 'MP1' union all
SELECT 2, 'fact2', 'MP2' union all
SELECT 3, 'fact3', 'MP4' union all
SELECT 4, 'fact2', 'MP3' union all
SELECT 5, 'fact3', 'MP5' union all
SELECT 6, 'fact4', 'MP6' union all
SELECT 7, 'fact2', 'MP4' union all
SELECT 8, 'fact5', 'MP7'
)
  ,  CTest AS
(
select Id as IdDeb, Id, RefFact, RefMp, 1 as Cpt,
       cast(Id as varchar(max)) as pth
  from t
 union all
select c.IdDeb, t.Id, t.RefFact, t.RefMp, c.Cpt + 1,
       c.pth + '-' + cast(t.Id as varchar(max))
  from t
       inner join CTest as c
         on c.RefFact = t.RefFact
         or c.RefMp   = t.RefMp
 where charindex(cast(t.Id as varchar(max)), c.pth) = 0
)
  select Id, RefFact, RefMp,
         dense_rank() over(order by min(IdDeb)asc) as numDossier
    from CTest
group by Id, RefFact, RefMp
order by Id asc;
 
Id          RefFact RefMp numDossier
----------- ------- ----- --------------------
1           fact1   MP1   1
2           fact2   MP2   2
3           fact3   MP4   2
4           fact2   MP3   2
5           fact3   MP5   2
6           fact4   MP6   3
7           fact2   MP4   2
8           fact5   MP7   4

[Jamming Ed]

Merci Waldar,

Cette solution marche parfaitement bien. J'avoue ne pas tout comprendre pour le moment mais je vais y atteler mes neurones

A+