Update un peu difficile

Version imprimable

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:

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:

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 :cry:).

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

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:

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 :ccool:

28/04/2010, 07h26
Jamming Ed

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

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

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:

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:

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

29/04/2010, 16h07
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 :mouarf:

A+