Discussion :

[solitude]

Bonjour à tous,
J'ai ce pb que je n'arrive pas à résoudre en SQL Ensembliste. j'étais obligé de le faire avec le Curseur.
J'ai un bénéficiaire qui a 2 types de commandes:
Commande crédit : on crédite son compte.
Commande Débit : on débite son compte.
Toutes les commandes sont ordonnées par ordre décroissant par rapport à la date de la commande.
Il s'agit de trouver quelles sont les commandes Crédit auxquelles sont associées les commandes Débit en commençant par la commande Débit la plus ancienne.

CodeBenef	DateCommande	MontantCredit	MontantDebit	ReferenceCommande
10001	30/05/2017	0	90	AX
10001	12/05/2017	100	0	DZ
10001	10/04/2017	0	50	EF
10001	25/03/2017	0	100	SH
10001	10/03/2017	90	0	MN
10001	20/02/2017	80	0	GL

Le résultat final doit être:

CodeBenef	RefCommandeDebit	RefCommandeCredit
10001	SH	GL
10001	SH	MN
10001	EF	MN
10001	AX	MN
10001	AX	DZ

SH = GL (80 euros) + 20 (euros de MN) ---- > SH est liée à GL et MN
EF = 50 euros ---- > on Prends 50 des 70 euros qui restent de MN --> EF est liée à MN
AX = 90 euros -- > on prends les 20 qui restent de MN et 70 euros de DZ ---> AX est liée à MN et DZ.
Résolu avec un curseur, je voudrais pouvoir le faire sans le curseur mais en ensembliste.
Merci d'avance. si vous avez des idées pour aborder ce genre de pb. Vu le pb ça ressemble à de la récursivité. la ligne suivante dépend de la précédente.

[aieeeuuuuu]

Bonjour,

Voici une piste (tous les cas ne sont pas pris en compte)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
tmp AS (
	SELECT	*
			, SUM(MontantCredit) OVER(ORDER BY DateCommande) as SCredit
			, SUM(MontantDebit)	OVER(ORDER BY DateCommande) as SDebit
	FROM latable
)
SELECT Deb.ReferenceCommande as ReferenceDebit, Cred.ReferenceCommande AS ReferenceCredit
FROM tmp Deb
	LEFT JOIN tmp Cred
		ON		Cred.montantCredit > 0
		AND		Cred.SCredit > Deb.SDebit - Deb.MontantDebit 
		AND		Cred.DateCommande < Deb.DateCommande
WHERE Deb.MontantDebit > 0

[solitude]

Merci pour votre idée.
J'ai testé des bouts de code qui ressemble un peu sauf que le problème pour les lignes suivantes ça ne fonctionnait pas.
Je vais tester votre idée.
Merci.

[SQLpro]

Je suis arrivé à la même chose que toi...

Avec la table :

Code :

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

1
2
3
4
5
CREATE TABLE X (CodeBenef INT, 	
                DateCommande DATE,
                MontantCredit  DECIMAL(16,2),	
                MontantDebit  DECIMAL(16,2), 	
                ReferenceCommande VARCHAR(16))

Les données :

Code :

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

1
2
3
4
5
6
7
INSERT INTO X VALUES 
(10001,  '30/05/2017', 	0,     90,   'AX'),
(10001,  '12/05/2017' ,	100,   0,    'DZ'),
(10001,  '10/04/2017' ,	0,     50,   'EF'),
(10001,  '25/03/2017' ,	0,     100,  'SH'),
(10001,  '10/03/2017' ,	90,    0,    'MN'),
(10001,  '20/02/2017' ,	80,    0,    'GL');

La requête :

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
WITH
TD AS 
(
SELECT CodeBenef, DateCommande, MontantDebit, ReferenceCommande,
       SUM(MontantDebit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulDebit
FROM X WHERE MontantCredit = 0
),
TC AS 
(
SELECT CodeBenef, DateCommande AS DatePaiement, MontantCredit, ReferenceCommande,
       SUM(MontantCredit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulCredit
FROM X WHERE MontantDebit = 0
)
SELECT *, CumulCredit - CumulDebit AS SOLDE
FROM   TD 
       LEFT OUTER JOIN TC
            ON  TC.CodeBenef = TD.CodeBenef
                AND TC.montantCredit > 0
		AND TC.CumulCredit > TD.CumulDebit - TD.MontantDebit 
		AND TC.DatePaiement < TD.DateCommande
                AND TD.MontantDebit > 0
ORDER BY TD.DateCommande, TC.DatePaiement;

Le résultat :

Code :

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

1
2
3
4
5
6
7
CodeBenef   DateCommande MontantDebit    ReferenceCommande CumulDebit   CodeBenef   DatePaiement MontantCredit    ReferenceCommande CumulCredit    SOLDE
----------- ------------ --------------- ----------------- ------------ ----------- ------------ ---------------- ----------------- -------------- ---------
10001       2017-03-25   100.00          SH                100.00       10001       2017-02-20   80.00            GL                80.00          -20.00
10001       2017-03-25   100.00          SH                100.00       10001       2017-03-10   90.00            MN                170.00         70.00
10001       2017-04-10   50.00           EF                150.00       10001       2017-03-10   90.00            MN                170.00         20.00
10001       2017-05-30   90.00           AX                240.00       10001       2017-03-10   90.00            MN                170.00         -70.00
10001       2017-05-30   90.00           AX                240.00       10001       2017-05-12   100.00           DZ                270.00         30.00

Tu as juste oublié la jointure sur le bénéficiaire

A +

[SQLpro]

Au final avec un jeu de données plus complet, on peut simplifier avec :

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
WITH
TD AS 
(
SELECT CodeBenef, DateCommande AS DateDebit, MontantDebit, ReferenceCommande,
       SUM(MontantDebit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulDebit
FROM X WHERE MontantCredit = 0
),
TC AS 
(
SELECT CodeBenef, DateCommande AS DateCredit, MontantCredit, ReferenceCommande,
       SUM(MontantCredit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulCredit
FROM X WHERE MontantDebit = 0
)
SELECT *, CumulCredit - CumulDebit AS SOLDE
FROM   TD 
       INNER JOIN TC
		      ON  TC.CodeBenef = TD.CodeBenef
		      AND(    (TC.CumulCredit > TD.CumulDebit  - TD.MontantDebit  AND TC.DateCredit <= TD.DateDebit)
                 OR (TD.CumulDebit  > TC.CumulCredit - TC.MontantCredit AND TC.DateCredit >= TD.DateDebit) )
ORDER BY TD.CodeBenef, TD.DateDebit, TC.DateCredit;

A +

[solitude]

Franchement Merci beaucoup.
Je n'ai pas encore regardé vu que la procédure est juste en recette.
j'ai encore un petit peu de temps pour la modifier pour la mettre en production sans le curseur.
ICI les DBA n'aiment pas trop les curseurs. mais je comprends.
Merci beaucoup.

[solitude]

Merci beaucoup pour vous deux.